Carpe Datum

Quote of the Day: On Keeping Ease in its Place

I admire people who have overcome difficulties to achieve. I find so few people today that can stand any adversity - people whine and complain at the slightest inconvenience. In the past character seemed to matter more, and Helen Keller was one such person of character. She said: 

"Character cannot be developed in ease and quiet. Only through experiences of trial and suffering can the soul be strengthened, vision cleared, ambition inspired and success achieved."  - Helen Keller

PowerShell and SQL Server: Script all Tables

This is a script that I found/put together/re-arranged that will script out all of the tables from a database - in this case, Adventureworks2008. You need to change the BWOODY1 part to the name of your server, and the SQL2K8 part to your Instance name. You can change the database name as well, of course, and a better method would be to make a function out of this and feed those variables in.

There are other ways to do this, but this script illustrates setting some of the scripting options. You can look up the SMO model for all of them.

As always, test this script out thoroughly, and understand what you're doing before you put this on a production system. Also, don't run with scissors, and don't open attachments from people you do not know:

# Script all tables in the AdventureWorks2008 database

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'

$db = $s.Databases['AdventureWorks2008']

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $True

$scrp.Options.ClusteredIndexes = $True

$scrp.Options.DriAll = $True

$scrp.Options.ScriptDrops = $False

$scrp.Options.IncludeHeaders = $True

$scrp.Options.ToFileOnly = $True

$scrp.Options.Indexes = $True

$scrp.Options.WithDependencies = $True

$scrp.Options.FileName = 'C:\TEMP\AdventureWorks2008Tables.SQL'

$scrp.Script($db.Tables)

SQL Server Best Practices: Auto-Shrink Should Be Off

SQL Server is one of the easiest databases to maintain because of all of the automatic settings it has, but as I mentioned with Auto-Close, some of them should be left off. The Auto-Shrink setting is another.

That might surprise a few people. You might think from the name of this operation, that you would want your databases to automatically reclaim any space they take whenever they can. And if SQL Server will handle that for you, all the more wonderful. But in fact, shrinking a database can cause your Indexes to be fragmented, especially if it happens a lot. I never shrink my databases unless I have a huge deletion of data, and I know that the data won't come back. That's a pretty rare event, and when it does happen I run the shrink operation manually and rebuild my indexes after.

My friend Paul Randall has a great explanation of why this happens, and another way to reclaim that space without running shrink at all: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

What's a Microsoft "Fast Track" for Data Warehousing?

Microsoft is a platform company, meaning that we don't often sell pre-packaged solutions, but rather tools for you to make them. In other words, we sell Microsoft Word, we don't type the letters for you.

But in some cases you might like to have a little more guidance than just "Here's a database product. Go nuts." You might want to know what OS goes with which edition, and you might even want to know what hardware configuration to buy.

To help you with that, we came out with this concept of a "Fast Track". It's basically a manifest of hardware and software to buy and a list of how to set it all up, that ends up with a solution you can use for Data Warehousing. I really like the idea, and I hope we extend it to the database platform, although I've not heard anything to that effect. At any rate, you can check it out here: http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx

Grid Painting and Database Projects

I'm not very good at drawing. My drawings of people and objects with depth usually looks like what your 5th grader does, but not the talented 5th grader. But I found a trick that helps me draw not only depth, but complex things as well.

It involves taking a picture you want to duplicate and laying out a grid of lines over it. Then you just draw the same grid on another sheet of paper, and instead of trying to draw the entire person, landscape or horse, you focus on one little box in the original grid and draw the squiggly lines you see there. Then move one box to the right and do that again, and again, and again, and eventually you've drawn the entire scene. Carefully erase the grid lines that are left, and you've drawn better than you really can by freehand!

I've been asked by some DBA's how to approach a complex project, and I think the trick is to do the same thing. I lay out an imaginary "grid" or work to do. The first grid I lay out is really large:

• Design the database solution
• Implement the solution
• Set up maintenance (both database and code-related) for the solution
• Set up High Availability (HA), Disaster Recovery (DR), and Security for the solution

Then I set up a finer "grid" on each of these - Design breaks down into:

• Define the business requirements
• Look for nouns and verbs
• Turn those into tables and relationships
• Tease out the columns for the tables
• Normalize the tables
• Set up the Referential Integrity...

And so on. I do the same for each of the main "grid" segments, and then off I go. Sure, this is just turning big problems into smaller ones, but I find that it really helps - it removes the stress, focuses the attention and allows me to create a fairly accurate timeline for my part of the project.

Quote of the Day: Aesop On Learning

It is truly remarkable how many things we learn from the ancients - and how much we don't:

"Good judgment comes from experience, and experience--well that comes from poor judgment." - Aesop

Use PowerShell to Backup All User Databases

This script will back up all user databases - you need to change the BWOODY1\SQL2K8 part to your server\instance name, and of course, you should only run this on a test system until you completely understand it.

Unfortunately, the SQL Server PowerShell Provider doesn't make this very easy - and there may be a better way to do this once I research it more.

Also - this script was adapted from one given to me earlier, and unfortunately I don't recall the source. If this is partly your work, please add a comment here for attribution:

# Performs a Full backup followed by a transaction log backup on all user databases

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'

$bkdir = $s.Settings.BackupDirectory

$dbs = $s.Databases

$dbs | foreach-object {

    $db = $_

    if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {

        $dbname = $db.Name

        $dt = get-date -format yyyyMMddHHmmss

        $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')

        $dbbk.Action = 'Database'

        $dbbk.BackupSetDescription = "Full backup of " + $dbname

        $dbbk.BackupSetName = $dbname + " Backup"

        $dbbk.Database = $dbname

        $dbbk.MediaDescription = "Disk"

        $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')

        $dbbk.SqlBackup($s)

        if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {

            $dt = get-date -format yyyyMMddHHmmss

            $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')

            $dbtrn.Action = 'Log'

            $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname

            $dbtrn.BackupSetName = $dbname + " Backup"

            $dbtrn.Database = $dbname

            $dbtrn.MediaDescription = "Disk"

            $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')

            $dbtrn.SqlBackup($s)

            }

        }

    }

SQL Server Best Practices: AutoClose Should be Off

When SQL Server "opens" a database, resources are assigned to maintaining that state. Memory for locks, buffers, security tokens and so on is assigned, and there is associated CPU and even a little I/O. When connections are made to the database, these resources are ready and waiting. The AutoClose setting dictates how these resources are handled. If this setting is ON, then when the last user connection that disconnects, SQL Server releases all of those resources back to the server. That might sound like a good thing, but if you think about the pattern of connections, the "last" connection doesn't mean that someone might not connect one tenth of a second later. In that case, SQL Server would shut all of the resources down, and then have to spin them right back up again. As a result, the server makes the connection wait, which makes the system seem slower.

So the best practice is to leave this setting OFF. In fact, SQL Server 2008 Books Online states that this setting is going away anyway, so you should have them turned off (http://msdn.microsoft.com/en-us/library/ms135094(SQL.90).aspx).

Is Your Infrastructure Crumbling?

I watched a documentary (of sorts) last night called "Crumbling America" that talked about America's infrastructure issues. They detailed how roads, bridges, electrical grids and water systems are having serious issues and are basically falling apart. I think they were sensationalizing a bit, but it was interesting to note that America spends only 4% of it's income on infrastructure now, down from a high over over three times that amount. Even the "stimulus" plan containing almost a trillion dollars has less than 9% of the money earmarked for infrastructure.

I found all this interesting because it has corollaries to our IT infrastructure. While I can't control our federal infrastructure, but I can affect my local IT infrastructure, especially the database. I pulled out three things from the television program that I thought was applicable to IT. They mentioned three main issues with infrastructure that I think we should evaluate in our own systems:

1. The system had a design flaw.

The first thing that they pointed out about a road or bridge that failed was that in some cases the engineers decided to go with a lighter steel plate, or a different kind of surface that turned out not to be strong enough or last long enough. This holds true in IT as well. I've seen many systems that just had a fundamental design flaw that caused an issue later. What can we do about this? Two things: educate yourself about good design patterns and implement them in your own code, and evaluate any "canned" solutions your company buys and ask the hard questions about the design of the software. After all, you have to live with it when it is gone.

2. The system can't handle the load or speeds required in a modern environment.

In the case of a water system or electrical grid, the initial design or even the overall arrangement of the components cannot handle how much work required from the users. This of course has obvious parallels to IT. The thing we can do as DBA's and Developers is to do a good job of projecting load, and constantly monitoring the systems to make sure we know when we're reaching a limit.

3. The system upgrades keep getting deferred.

Keeping a road, bridge or other system maintained and upgraded is very expensive and disruptive. Politicians take the easy way out and defer that maintenance over and over. This leads to eventual disaster. It's the same in IT. As you monitor your systems, make sure you push for upgrades along the way. I run into firms every day that are still using SQL Server 2000 - even though it is out of maintenance, has lower security, and does not perform as well as the newer versions. I'm told "my vendor won't upgrade" or "it's working just fine" and it reminds me of the inspectors on the bridges that fail.

There are more parallels, but I think you have the idea. You can take almost any system that handles "traffic" or load and learn from it. The question is, will you?

Help Me Help You

Before I start this post, let me say that it is not pointed at any one person - if you've asked me for help recently, this isn't about you!

The programming and administration computer disciplines are fairly unique, in my mind, in one interesting way: we help each other a lot. Since the beginning of the DARPANET, which eventually became the Internet, administrators have used e-mail, newsgroups and other methods to chat with each other - "hey, I'm having this issue....anyone know how to handle that?" It was a form of necessity, since everyone had to come up to speed quickly on a technology that wasn't documented yet because it didn't exist yet.

And that tradition continues today. Those of us that work at Microsoft, and of course tons of people who don't, participate in forums, e-mail exchanges, newsgroups and other forms of user-to-user assistance. But many times I'm not able (or very willing, at least) to help someone because they get in their own way. So I thought I would create a blog entry that gives you some ways to get help from those willing to give it. If you'll follow these simple steps, you'll probably find more of your posts answered:

1. State your question clearly.

Instead of "I tried this terrible software and it doesn't work right - how do I fix that?" be very specific: "When I implement the Maintenance Plan Wizard, on step three of the Wizard I get error 980234 from SQL Server Management Studio. Has anyone else encountered that, and where should I look to start fixing this?" The more precise you are the better the odds that someone can help you.

2. State what you've done to fix the problem so far.

Explain all of the steps you've taken to research or correct the issue so far: "...so I looked up error 980234 in Books Online, but it doesn't seem to apply because I'm not on a cluster. I searched on "non-clustered" and "980234" and I didn't come up with any hits." If you haven't even read the manual or looked at any one of the thousands of pages of documentation on the subject, don't ask the question. Do your homework first, put some effort into finding the answer.

On this note, don't expect others to re-write your code or do your work for you. There's a very big line between helping you find the answer to a question and doing your job - that's why they pay you, not me.

3. Be professional.

No one cares how much you hate a vendor or software product. No one cares how much time you've spent on this or how frustrated you are. None of that matters - what matters is that you need your problem fixed. If you ask a vague question, and insult Microsoft (or any vendor, actually), I just move on to another question. And don't demand and answer: "I need someone to help me fix this now!" If you can't be nice, why should someone else?

Quote of the Day: On Taking Chances

Sometimes when things are tight people take fewer chances - but I think that's the very time you should go after the big things:

"Why not go out on a limb? Isn't that where the fruit is?" - Frank Scully

SQL Server Best Practices: User-Defined Schemas

Schemas are a feature that gained wide use in SQL Server 2005. They are essentially containers that allow some objects to be grouped together. Actually, there have always been schemas in SQL Server, it's just that we only exposed one - called "dbo" (short for Database Owner). In SQL Server 2005 and higher, you can now create your own schemas and then put objects there - and you should. Leaving everything in the dbo schema is like putting everything in the root directory of your hard drive. We use folders there to group information and programs, and you should do the same using schemas with your database objects.

In addition, you should assign each user a "default schema". This ensures that if they create an object without specifying a schema, it will automatically go into their assigned container.

And there's another thought here as well. Within your code, it's best if you specify the schema name (even if it is the default schema or some other mechanism) along with the object when you select or activate it. It saves the Query Processor a step from having to resolve the schema name out by itself.

You can find more here:

http://msdn.microsoft.com/en-us/library/dd283095.aspx

http://msdn.microsoft.com/en-US/library/ms190387.aspx 

http://www.ignew.com/post/SQL-Server-Select-Query-Best-Practices.aspx

It Is The Wise Technologist That Makes Complicated Things Simple

They say that DBA's are people who put other people into two categories: People who put people into two categories, and people who don't.  I think lots of us put people into categories, not just DBA's.

I've run into a couple of "types" of DBA's - or any other IT profession for that matter. Some people (I have a preacher who is really good at this) can take very complicated things and explain them in a basic way. In fact, Einstein is supposed to have said "You don't really understand something unless you can explain it to your grandmother." Only when you understand something, really and truly understand it, can you make it easier for others to understand - at least at the level they need to understand it. In another interview, Einstein was said to have explained to a non-technical audience about the fact that time can be different for two people like this: "Time is different based on which side of the bathroom door you're on."  That word picture immediately conjures up what he's talking about, at least at the level you might care about.

I've known some DBA's who do this well. They follow standards, document things, and generally perform an amazing amount of work to make the whole data infrastructure look seamless, trouble-free, and simple. They are patient, they take their time and explain things to both managers and propeller-hats at the levels they can understand. They make complicated things simple. Keep in mind that this is the harder path - and benefits others more than self.

Then there's the other kind. This DBA has learned how to do this or that, does it their own way, doesn't document and scoffs at "standards". I've inherited systems from these folks and it's basically a reset of everything to know what's going on. They remind me of the BOFH (look it up) from Jurassic Park that had the system so screwed up no one could figure it out. They make complicated things complicated, believing that this adds to their value. Their systems work, but only if they are there to "Captain Kirk" the thing into submission. They do things that benefit themselves more than others.

Most of us would rather be around the first type of technologist. But it's interesting that I've seen the second type as a highly-valued resource - especially by the management team. They think the second type is "really smart" because what they do looks - you guessed it - complicated. The first type of DBA that makes everything look simple isn't valued as highly because they've taken the time to make things clear and well run. It looks simple, so it isn't highly valued.

If you're a manager, you can find out which DBA you're working with very quickly. Just ask: "How would you make things better around here?" If the answer is self-centered, you're not working with a wise DBA - skillful perhaps, but not wise. If they pause and think the question through, and come up with a suggestion that helps not only themselves but the rest of the team and the organization at large, then promote that person as soon as possible. Those kinds of people don't come around very often.

SQL Server Management Studio - Copy With Headers

I'm writing this on the train to Seattle to visit with a customer this morning - I'm thankful for Wireless! Although setting up a wireless network for a train is a really big thing, sometimes it's the smaller things that make us happy.

In SQL Server Management Studio (SSMS), you can open a query window and have the results sent to a grid output. Sometimes you want to copy some (or all) of those rows out to another application like Excel or Word. And one of the most frustrating things is that you don't get the headers of the rows of what you just copied. Well, it's a problem no more. Run a query, select your rows, and then right-click them. We've now got a menu item called "Copy with Headers" that does exactly what it says.

Oh, there's another feature that you might not be aware of - you can hold down the CTRL (Control) key and click on various cells and then right-click those for a copy or copy with headers.

Moving Tables Between Filegroups

I got an e-mail in response to the Best Practices blogs on Filegroups. I've gotten other e-mails similar to this one, so I thought I would answer (or try to) all of them here. Here's the e-mail:

"I recently started a new DBA job.  When I started the company had recently purchased new hardware and had moved their existing SS 2000 db to SS 2008.  But they did not change the file structure (location of data, index, log files) they kept them on the same drive except for the temp db is on a separate drive.  There are three separate drives (in a RAID 5 of course) C:\, D:\, F:\

 

C:\ contains the OS and some Primary data filegroups.  D:\ contains data file groups, index files groups, and the backup files are saved here until moved off to a backup server.  F:\ contains the tempdb files.

 

How does one modify a production db server to utilize the three drives without harming transactions?  In other words, how is this done seamlessly?

 

Thank you for you insight in advance!"

There are a few things happening here at once, so let's tease this out a bit. First, if there is a SAN with one Logical Unit (LUN) presented to the server as a physical drive, then the drive letters and the separation really don't matter. Once again, you might try and speak with the SAN admin to have them understand how SQL Server uses the file system, and they might be willing to break up that LUN for you into smaller LUNs, which you can map to drive letters, and then you can map the SQL Server Filegroups to those, and then put Files and then Objects in that (whew!).

That brings up a point that people sometimes get confused on. Files are not the same thing as Filegroups. There's always one Filegroup created by default, and multiple files (which might even be on separate drives) can go in that. You can create more Filegroups, which contain yet more files. The only thing you can specify for a table or index (those are the main data-bearing objects) is the Filegroup, not the files. The files fill up automatically, like water poured into glasses at the same rate. So you're working here with the Filegroup, not the files.

Now to the next issue. How do you move things once they are already created? There are a few options - some easy, some, well, less easy. To move the tempdb database (often a good idea) you can use the ALTER DATABASE statement. There's a decent article on that here: http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/ 

For backups and other maintenance tasks that create data (like exports), just change the statement that backs up the database to another location. Easy Peasy.

How about Indexes? These are also easy to move - sometimes. Just follow the steps here: http://msdn.microsoft.com/en-us/library/ms175905(SQL.90).aspx. Note that if the index is Clustered, then the table *is* the index. So by recreating the index onto another Filegroup, you've effectively moved the table. When is it not easy? Well, if the index is huge, it can take a really long time. And as always, you should test and backup everything before you make changes like this.

The most difficult? Tables. You can use SQL Server Management Studio and just change the file location. That works (almost) every time, so once again, back up before you start. And also make sure you have the downtime for larger tables, as they will possibly be unavailable during the move. You can also script the table, create it on the new Filegroup, and then transfer the data over. In effect, that's what SSMS is doing under the covers. But it isn't trivial to do this - you need to worry about keys, renaming the tables and so on.

There's more on this process in this great article on SQL Tips: http://www.mssqltips.com/tip.asp?tip=1112