Buck Woody : Microsoft, Development, Design

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?

Know Your Product Specifications

BuckWoody — Wed, 13 Jan 2010 14:57:01 GMT

As the Data Professional in your organization, the rest of the org looks to you to ensure that the system can handle what the business requires. To do that, you need to know two things: what the business requires, and what SQL Server can do.

But of course there’s a bit more to it than that. Knowing the business side of the requirements – well, I teach an entire course on that. But knowing what SQL Server can do is something you can find out on your own.

SQL Server comes in versions, which are released based on date, and editions, which are based on features and capabilities. It’s that last part that I want to focus on today.

As Microsoft SQL Server matures, you’re going to see even more separation between what each edition of SQL Server can do and where it should be used. In the past, most folks have only focused on three editions – Express (the “free” one), Standard, and Enterprise. The rule of thumb was that if Standard was good enough at the moment, put it in. And it is true (and a good thing) that you can upgrade from one edition to another fairly easily.

But as time goes on, we should spend a little more time understanding what each edition does, what it’s features and capabilities are, and where and when we should put them in. As I study this information, I’ll throw in my 2 cents and you can as well based on what you see. One thing I’ve found so far is that once I have the business requirements, there’s a mix of what I can write in code and what might already be included in a different edition. It’s important to look long and hard at that choice – writing a feature on my own is certainly cheaper in the short term than moving to a “higher” edition, but in some cases it makes sense to let Microsoft handle that lifting.

These links are ones that you should bookmark and take a peek at periodically. They are the “header” links for more information on those features and capabilities:

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms143287.aspx

SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms143287(SQL.105).aspx

In addition, you might start learning a little more about SQL Azure. I’ll talk more about that later.

Aren’t DBA’s Just System Admins for Databases?

BuckWoody — Mon, 30 Nov 2009 16:37:32 GMT

Last week I ran into an argument I’ve had since I left the mainframe space decades ago. A developer told me “DBA’s don’t design databases.” The inference was that DBA’s (i.e., Database Administrators) only worry about hardware, security, OS, database backups, things like that. He seemed amazed that a DBA would ever do “data” work.

It may be the name. Perhaps the “admin” part confuses developers. Also, it is true that in some shops, a systems admin does double duty with Windows, SQL Server, and perhaps even mail and web admins.

But there are a LOT of DBA’s, or as the term I like to use, “Data Professionals”, that actually DO get down in the trenches and design databases, write Transact-SQL code and stored procedures, and do almost everything in the database other than write middle-tier or User Interface (UI) code. Some I know even do that.

So what if there is a miscommunication on this? Well, the ramifications can be huge. For one thing, there’s a lack of respect. That’s not called for ever, no matter what anyone’s role is. Also, one of the most impactful areas in a database is the design. When a DBA is asked to export data, tune a process, or troubleshoot an issue, it invariably involves the design. So when a DBA doesn’t get to do the design, they have to live with the results. And anything you’re responsible for when you don’t have the authority over is a recipe for stress.

Another issue is that DBA’s “inherit” all kinds of data structures form around the company. From Microsoft Access to Excel, to amateur Business Analysts creating databases in the Express Editions, they deal with bad design day after day. The newer “modeling” languages that are coming into vogue will make this problem much worse. These languages do not take scale, extensibility, security or performance into account – they just make sure that the data ends up in the right place for that particular design, which is a recipe for data disaster when the “small application” the developer writes becomes a “mission critical” system the DBA has to troubleshoot at 2:00 A.M.

So in case you’re a developer, and in case you think DBA’s “just do admin” – think again. DBA’s spend their whole day in this world, and can be a valuable asset to your development efforts. Bring them in early, bring them in often, and whatever you do – don’t design alone. Business Analysts, Developers and Data Professionals are needed to make a good, sustainable, secure, well-performing database.

Changing the Primary Key After You Have Data

BuckWoody — Tue, 24 Nov 2009 13:25:22 GMT

Yesterday I blogged about changing a Primary Key (PK) during the design phase, and before you have data in the database. Even then, it’s not trivial to change the data type or column(s) that make(s) up the PK. When you have data in that Primary Key and/or you have Foreign Keys (FK) that point to a PK field, this becomes a much more involved process.

First, you MUST take a complete backup of the system, and you MUST do this work on a development system. You’re going to be manipulating base data, and in the worst kind of way. Why do I say that?

There are three kinds of data problems. One is physical data corruption, and once you restore it (I don’t trust repair with data loss) then you know where the “good” data stops and starts. You know that the FKs are pointing to the right PKs, and that the data is readable.

The second kind of data problem is “incorrect” entry, meaning that you put in “Buck Woodie” when you meant “Buck Woody”. This is also often correctable, as your Declarative Referential Integrity (DRI) will still keep that record together. It’s a simple matter to locate and correct.

But the third kind of data problem is the worst. In this kind, the FKs point to the wrong PK, or not at all. Or perhaps some of the FK data is missing. The reason this is the worst is that the data cannot be trusted – did Buck Woody really pay for that purchase or was that payment from a different record? If the links are incorrect, everything will look fine, but it won’t be correct.

So in the situation where you want to change the PKs when you already have data, one of the steps is to re-point the FKs to the proper PKs. Get this wrong and you’ll have “dirty data”. The only recourse there is to restore the backup and start over.

I’m assuming that you have a REALLY GOOD reason to make this change, and that you’ve taken that backup and you’re on a test system. As Hannibal Lector would say: “Okeedokee – here we go.”

I’ll describe two possible choices. In the first, understand and document the PK and FK relationships for each table. Then create queries that tie out the INSERTS for that data in the correct order, and include all fields for both tables, including the PKs in the parent table and the FKs in the child tables.

With those INSERT statements made, drop the PK and FK constraints on all of the tables using the script I mentioned yesterday. Make your change to the data type (or field) in the tables. Then edit the INSERT statements to have the new value types, ensuring that the “sets” or records are kept together by keeping the FK’s pointing to the right PKs. Then re-apply the PK and FK constraints, watching for errors. With backups and scripts, you can make corrections along the way.

The second method works much as the first, with the exception of using the INSERT statements. You can use SSIS or a third-party transfer process to actually move the data and change the data type or values “in flight”, although this requires more thought and planning in my experience.

In any case, you should test the results thoroughly and ensure that you’re getting the data you expect. Communicate what you’re doing, and be ready to fall back to your backups at the first sign of trouble.

Oh – since SQL Server 2005, you’ve been able to “relax” constraints to insert data. That won’t help you here, since you’re changing the entire key for whatever reason. That’s only meant to allow you to get data in when the key doesn’t change.

OK – feel free to comment on this post, since there are other ways I didn’t cover. But if you’re reading my material or any of the comments, make sure you test it yourself – I might have missed something, no one is perfect, your situation may vary. Once again, you can see why I value a good design and ERD so highly – that way you never have to get here to begin with!