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

     

  • 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.

  • Ping Back: Checklists, Recipes and Algorithms

More Posts « Previous page
Privacy Statement