THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

  • Planning Database Servers

    I’m about to purcahse servers for my software start-up (providing specialized SaaS CRM – i.e. we’re hosting other organization’s data) and since this is a good bit a change from my pocket, I’d like to survey this group. I’m purposefully wording the questions to obfuscate my opinion.

    1. Any preferences on DELL rack servers? Servers to avoid? Any experience with the DELL R610?
    2. Do you use SATA, SAS 7.2K RPM, SAS 10K RPM, or SAS 15K RPM drives?
    3. If you’re using the new superfast SSD drive would you still RAID the drives?
    4. Assuming all stored procedures and a normalized database with a nice new Quad core Xeon X5500 and pleanty of RAM, about how many concurrent users would you guess it would support?
    5. Do you use NICs with TOE on your database servers?
    6. Do you use redundant power supplies on your database servers?
    7. What method of failover server do you prefer, log shipping or database mirroring?  (assume that Clustering is out of the question)

    many thanks in advance...

  • 53 MVPs, WarChild.org, and SQLServerMVPDeepDives.com

    Responding to BillG’s MVP Summit challenge to “Do philanthropy where you are,” The SQL Server MVP Deep Dives book is a collaboration of 53 MVPs sharing their expertise and passion for SQL Server.

    This is an all-volunteer book. All author proceeds are going to WarChild.org – an organization that helps children traumatized by war. Because this is a book for charity, Manning Publications wanted to also donate and gave us a higher than normal royalty. In addition, if you purchase the book through this link: www.SQLServerMVPDeepDives.com then the purchase will also count toward Warchild's Manning affiliate account and Warchild will receive an extra 10% of the purchase.

    Save 50%: purchase from the website by the end of Sept and use the code pop0928 at the checkout

    If you’re going to PASS:  The first printed copies will be shipped directly to the PASS Summit Bookstore. We’re planning an official book launch at PASS. If you want to get your copy signed by every author at PASS then you must purchase a copy at the PASS Bookstore.

  • SQL Azure - hands on

    As I’ve blogged before, I like the concept of cloud database services. I think that SQL Azure is the right direction. I’ve also blogged that I’m starting a CRM SaaS software startup company. My original business plan had SQL Azure as the hosting service. I like the built-in redundancy and availability.

    Last week I took the SQL Azure CTP for a test-drive. It took me about 2 hours to fix all the incompatibilities with my code, and move my test database to SQL Azure. And Wow it’s very, very fast. Did I mention that it’s fast? I really like SQL Azure.

    But, sometimes tech decisions need to be driven by business realities. I’ve been crunching numbers and for my business model, SQL Azure is just too expensive. I can buy some nice Dell servers and pay for co-location in downtown Colorado Springs that will have dual power grids and connections to three different internet backbones in the same building for ½ to 1/3 of what SQL Azure costs. As a disclaimer, I intend to host several 5-25 Gb databases each with only a few users, which makes it more efficient for me to host several databases on a single server than pay for each SQL Azure database. If your business model is different then SQL Azure may make more sense for your bottom line.

    Since I’ve spoken up so loudly for databases in the cloud, I figured it was only fair to report my current thinking on the subject. So while I’ll keep testing SQL Azure, and I think it’s good tech, this startup is sticking with a traditional SQL Server solution until the SQL Azure pricing model fits my business plan.

  • 10 Big Database Ideas (take 2)

    If you missed our 24 Hours of PASS session on database design (read the Network World Review), Louis Davidson and I are presenting a second take next Tuesday (Sept 15th) at 8pm Eastern Time.

    Register now for the second take.

    PASS Post-Con: Advanced Relational Database Design

     

  • 24 hours of PASS !

    Louis Davidson aka Dr. SQL and I just finished testing the link-up for 24 hours of PASS. Live Meeting automatically switches the video based on who's speaking! who knew?

    Anyway, we're psyched – the Ten Big Ideas of Database Design starts at 8pm ET. Louis and I have a blast co-presenting at conferences – we did DevLink a few weeks ago – and we’re co-presenting a post-con at PASS in Seattle.

    If you’re not signed up for 24 hours of PASS – it’s freeeeee – why wouldn’t you watch?

  • playing "Let's Pretend" with SSN data

    The trouble with surrogate nulls (storing real data and pretending that it represents missing data) is that you never know when the real data is going to be interpreted as real data.

    Here’s a twist on the surrogate null issue: a case of shorter SSN numbers from Micronesia being padded with leading zeros - presumably a software requirement – which comes causes all sorts of frustration for those with SSN numbers that actually do begin with zeros.

     

    http://news.slashdot.org/story/09/08/19/2220209/SSN-Overlap-With-Micronesia-Causes-Trouble-For-Woman

     

    There are two lessons here:

    First, respect data and don’t presume that real data can pretend to represent missing data, even for a few digits, without being found out eventually.

    Second, data decisions should be made by a data architect with the experience to anticipate and prevent foolish errors.

  • UI Design Mock-ups made easy

    As I ramp up my new software company, one critical need I have is to communicate my UI ideas to clients and .Net programmers. I’ve used Excel and Visio for the task and wasn’t very satisfied with either. I figure that most of my blog’s readers are also database developer who might also need to help with project designs from time to time, so it makes sense to share the results of my search.

    http://lumzy.com/screens.php

    What I like most is that the resulting mock-ups look like mock-ups – the back of a napkin pencil sketch type of mock-ups. Because my goal is to invite discovery and iterations, a hand-drawn mock-up is better than a perfect polished Windows app look. It took me about 30 minutes to figure it out and crank out 2 form designs. Highly recommended.

  • SQL.alt

    Here's an interesting post analyzing several no-sql options from a developer POV.

    http://bjclark.me/2009/08/04/nosql-if-only-it-was-that-easy/

     

  • micro-ISV

    For the past several years I’ve focused on just researching SQL Server design patterns and speaking at conferences, user groups, seminars, writing (SQL Server 2008 Bible – 1,688 pages – will be at DevLink and in the bookstores Aug 24), and I’ve had a blast. SQL Server is about as much fun as a person can have (apart from marriage).

    As a research project, I’ve been playing with object-relational designs for the past 4-5 years. But I’ve found little traction with Nordic. Only about 50 people have ever emailed me asking questions about it. It seems the .Net crowd isn’t interested in a smart database, and the database group isn’t interested in objects.

    It’s time to make an oblique turn in my career. I‘ve pulled Nordic from CodePlex and I’m launching a micro-ISV focused on meeting the information organization needs of organizations that serve children in developing nations.

    To the original Nordic code I’ve added Data Policies – rules that track questionable data, Business Process Eventing – several things can trigger an event which creates alerts and actions, and Snapshots – some data is relatively static (e.g. DOB, name, place of birth), but lots of data is really a snapshot in time (e.g. height, weight, health, diet, education level). Nordic now has multiple types of snapshots that can be defined for any class. Snapshots really open up the data modeling for an object database. They can hold data like annual audits, annual profiles, and applications. And, snapshots can serve as roles.  Of course, since I’m a huge believer in object databases all these features support inheritance. Another cool thing about the generalization of an object database is that all these features – workflow states, associations, snapshots, data policies, alerts, and events are all generically searchable. I’ve gone through a few dozen iterations of the search proc adding more flexibility. All in all, I’m having a very fun summer.

    Monday I’m presenting a demo to a mid-sized client. I’m excited about having a real product that solves real problems. As much as I enjoy talking about SQL Server, I much more enjoy developing.

  • Business Process Design Q?

    I’m adding a business process feature to my app and before I start coding, I thought it might be a good idea to vet the design past a few smart yet critical folks.

     

    The basic idea is around the concepts of events and actions. An event might be something like a sale, or attending a seminar, or calling tech support. An action is a scheduled action that someone must perform or check on, or an automated action such as sending a letter.

     

    An event can be manually triggered or automatically triggered by a number of system functions or data points being such as a foreign key being applied (association), a data policy being fired or resolved (the app has a rule-based data policy feature), or a workflow state change (objects within classes can have workflow states). Lastly, another event being fired or resolved can trigger an event.

     

    When an event is fired it schedules a number of automated actions (n) number of days from the event.  Actions belong to an object and have a FK to the ActionConfig. 

     

    An action can be manually fulfilled or canceled, or it can be automatically fulfilled by another process, or by another association (FK) being applied to the object in question.

     

    EventConfig (name)

      |    \

      |   EventTrigger(the things that can trigger the event)

      |

    ActionConfig(events that are scheduled)

      \

       \  Object

        \  /      

      Action (the actual scheduled events for an object)

     

    The application already has a strong workflow state feature. Every class can have inheritable workflow states with a prescribed workflow state path (A -> B, B -> C, B -> D, C -> D, etc.). If the class has workflow states then the objects must have a workflow state. Workflow State is a part of the association design – think of it as a FK with intelligence. So there’s already some business process flow in the app.

     

    My question to you is, does this look robust enough for scheduling normal business process flow? What am I leaving out? Do the names event and action seem appropriate and obvious? Any other ideas?

     

  • Glenn Berry's way cool diagnostic DMV queries

    Last Thursday evening at the Denver SQL Server User's Group, SQL Server MVP Glenn Berry demoed his script of diagnositc queries. Way Cool.

    http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1446.entry

  • Slashdot: Interesting Thread about MySQL forks and db vs db debate

  • What do you want in a SQL Server Dev Seminar?

    With the SQL Server 2008 Bible winding down, I'm ready to begin planning another round of SQL Server seminars. Since this crowd is smart, opinionated, and vocal, who better to ask for advice? If you have a minute, I’d sure appreciate it if you took this quick survey:

    http://survey.constantcontact.com/survey/a07e2imxpqcfun2c6s6/start

     

  • Why use Stored Procedures?

    There are as many opinions for and against using stored procedures as there are database-related roles. I believe the position that is most qualified to speak to stored procedures is the data architect’s role. Data architecture is the role that studies the various methods, patterns, standards, and best-practices that result in databases that will, together, best serve the organization now and in the future.

    Don’t confuse data architecture with data modeling. Although data architects often do data modeling, data architecture and data modeling are different disciplines.

    Data architecture evaluates various database designs and implementations by several criteria: e.g. usability, scalability, integrity, extensibility, security, and availability.

    Of those six basic database criteria, there’s one that stands out as the most expensive to refactor or correct later: extensibility – the ability to modify the database to meet changing requirements. I’ve seen organizations with horrible database designs, that desperately need to re-develop that database, but they can’t. The database is so tightly coupled that the idea of making a slight change to the database brings management to its knees in fear. Dozens of applications, hundreds of reports, and a score of nightly ETL and maintenance tasks are tied directly to tables in the database. At one location, it takes a six person, highly qualified team six months to just modify a column. The result of tight coupling is a brittle database.

    The cost of a brittle database is enormous to an organization. On the corporate roadmap, the database is the primary roadblock. The organization can’t implement critical changes because the database can’t support those changes. The organization (both internal clients and IT) will try to work around the brittle database with additional databases in an attempt to find features. IT will try to wrap the brittle database with additional layers in an attempt to find loose coupling. But complexity breeds complexity and eventually the organization with multiple completing database solutions will try to apply a master database so there’s a single source for answers. All this takes years and millions of dollars and… never… really… works.

    The only real solution is an abstraction layer that fully encapsulates the database. Every database fetch, insert, and update must go through this access layer. Just as SOA provides this encapsulation for processes, the database needs the same black-box API. If the database team wants to refactor the database to improve some feature, it’s free to do so. If a new feature is added, the database team can add that feature and modify the API. It’s very clean, easy to refactor, and the database is now an on-ramp to the corporate roadmap.

    If you can agree that a logical abstraction layer is required for database extensibility, the next question is, “Physically, where should the abstraction layer be implemented?”

    Many will answer that it’s implemented in the programmer’s favorite language, and unfortunately that’s probably true.

    A data architect would ask this probing question, “ What’s the projected life of the data?” When I teach my data architecture seminars, or give conference talks on data architecture, I ask the question, “How many of you have data that’s five years old?” every hand goes up. “Ten years old?” a few hands go down. This progresses until eventually one guy has data that 40-50 years old.

    The point is: data has a long lifespan. If data has the lifespan of an elephant, then application languages have the lifespan of bugs. Think back just five years ago, what was the hot application language used to develop DAL layers? Ten years ago? Anybody up for writing a new web page that calls the COM+ DAL layer? Didn’t think so.

    The data architect’s answer to why use stored procedures is that T-SQL is only language that you KNOW will last as long as the data. A stored procedure that was written ten years ago is still just as easy to call as one that will be written today, or in the future. Anything less is building a long term solution on today’s fad, and it’s just plain foolishness.

    If you'd like to talk with me about this in person, I'll be at TechEd in LA this week, hanging out in the SQL Server pavilion.

  • Multiple Assignment Variables and Order By in SQL Server 2008

    A multiple assignment variable, sometimes called an aggregate concatenation, is a fascinating method that appends a variable to itself using a select statement. Though nearly undocumented, the method's been around since at least SQL Server 2000. It’s been bashed as a kludge and hack by some and revered as the embodiment of elegance by others. The primary complaint has been that the sort order of the concatenation is not guaranteed by the SQL statement. Using a multiple assignment variable method depended on the behavior of the engine instead of on the declarative nature of SQL - Horrors to even a non-purist pragmatic minded fellow such as myself.

    But, while writing about this technique for SQL Server 2008 Bible, I discovered  (are you sitting down?) that SQL Server 2008 does indeed respond to an order by in a multiple assignment variable query!

    The first query has an order by name:

    USE AdventureWorks2008;

     

    Declare @MAV VARCHAR(max)

     

    SELECT @MAV = Coalesce(@MAV + ', ' + Name, Name)

      FROM (select name, DepartmentID from HumanResources.Department) D

      order by name

    Select @MAV

     

    Result:

    --------------------------------------

    Changed Name, Document Control, Engineering, Executive, Facilities and Maintenance, Finance, Human Resources, Information Services, Marketing, Production, Production Control, Purchasing, Quality Assurance, Research and Development, Sales, Shipping and Receiving, Test Two, Tool Design

     

    Change the order by clause and re-run the query:

     

    Declare @MAV VARCHAR(max)

     

    SELECT @MAV = Coalesce(@MAV + ', ' + Name, Name)

      FROM (select name, DepartmentID from HumanResources.Department) D

      order by DepartmentID

    Select @MAV

    Result:

    --------------------------------------

    Engineering, Tool Design, Sales, Marketing, Purchasing, Research and Development, Production, Production Control, Human Resources, Finance, Information Services, Document Control, Quality Assurance, Facilities and Maintenance, Shipping and Receiving, Executive, Changed Name, Test Two

     

    I haven’t tested MAV and order by on SQL Server 2005 for quite some time. I don’t recall it responding to order by in 2005, but I could be wrong. In any case it works great in 2008. If this is a new improvement in 2008, then I sure missed it in the Microsoft presentations.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement