Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

The Database Design Process

BuckWoody — Thu, 14 Jan 2010 14:02:00 GMT

I need your help. I know how I create databases, and I’ve watched a lot of other data professionals follow their own processes for that, but I want to know how YOU do it.

I’ve written about the process I follow for a complete database design on InformIT (use the "Next" button at the bottom of these to see them all). Beyond starting with the business requirements and REALLY hammering that out, here is the general outline I use:

1. Pull out the nouns from the business requirements (“Client”, “FirstName”, “LastName”, “Business Name”, “Business Street” )

2. Group the nouns into “parent | child” sets (“Client: First Name, Last Name”, “Business: Business Street”)

3. Continue Steps 1 and 2 until you can’t do it any further. These are your tables and columns.

4. Set a value that uniquely identifies every record (line). This becomes the Primary Key. I normally use a “surrogate” key, but a natural key also works if you need it. I don’t like compound keys, but I’ll use them where they make sense. But the overall goal is that every key should be able to identify one “Buck Woody” from another “Buck Woody” record. If it doesn’t, I haven’t done steps 1-2 enough, or perhaps I’ve done it too much.

5. Examine each and every column and ensure that they are in the proper type. Check the business requirements to make sure. (dates are always dates, money is money, numbers are always numbers and so on) Repeat until everyone agrees.

6. Examine the relationships between the tables. Can there be many clients in a company? Can there be many companies for each client? This sets up Foreign Keys, and potentially other tables to solve many-to-many relationships.

There’s a bit more to it than that, and the business requirements side of things are where I actually spend the most time. If you get that wrong, the most beautiful design in the world won’t work over time.

I currently use DBDesignerFork for my design documents to coordinate with my business and development teams, which is not a perfect tool. But Microsoft doesn’t have a good one (we did in Visio, but we messed that up) and so this is what I have to work with. I don’t keep up with this diagram after I create it; it’s just a tool to help me communicate from business to dev to DBA’s.

So now here is where I need your help. Will you post a response here (if you design databases very often) that tells me:

1. What process do you follow?

2. How important are the business requirements?

3. What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?

4. What’s your biggest pain-point about designing? Not with SQL Server, mind you, just the designing part.

Thanks!

Search results matching tags 'Development', 'Process', and 'DBA'

Tools and Processes for “Fitting it all in”

The Database Design Process