SQL

Moving a standalone instance to a cluster while keeping the name and IP

A little while back I posted about how to move a standalone instance to a clustered instance using a different name and IP, but what do you do when you need to keep the same name and IP address?  This is a much more complex procedure to complete. Continued »

Auto shrink is just pure evil

If you don’t agree with the above statement please keep reading.  I’m write, and it’s important, I promise.

In order for the auto-shrink feature to be really effective it has to move data from the end of the file to the middle/front of the file so that it can chop off the tail end of the database file.  This causes extra load to be placed on the disk, and on the CPU as it is identifying the data pages which can be moved, then moves them.

It also causes extra fragmentation to happen within the database as the shrink operation does not preserve the fragmentation state of the indexes within the database.  Because of this the worst time to shrink a database is write after the indexes have been rebuilt.  Because of the extra space that is needed to rebuild indexes this is probably also the most common time to shrink a database on a regular basis.

My favorite reason to not shrink a database is listed directly in Books OnLine under the “Shrinking a Database” heading.  Under the Best Practices topic it says “Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.”.

So go and turn your AUTO_SHRINK settings to off like they should be and quit worrying if the hard drive icon in the My Computer window shows that it’s full.  Worry about about how much free space is within the database files, not the free space on the disk.   Fill the disk already.  It’s fun, and all the cool kids are doing it.

Denny

I’ve got some money for an upgrade, what should I upgrade?

So you are going along your normal day, and your boss comes up to you and tells you “We’ve got a few thousand bucks left in this years budget, what would you like to upgrade?”  Assuming that new 26″ monitors for your workstation are out of the question, the boss is probably talking about a server upgrade here so lets see what we can do. Continued »

How do you know when it’s time to refactor that database design?

Software developers love re-factoring code.  And why shouldn’t they.  It’s quick (sometimes) and when done correctly it’ll reduce the amount of code, and speed up application response time.  DBAs like re-factoring code as well.  We get the same benefits when done correctly.  Re-factoring the database schema on the other hand, is a frigging nightmare.

Changing around code is easy, moving 100,000,000 records from one table to another in a timely fashion isn’t.  It sucks, big time. Continued »

Hey sysadmins, quit trying to virtualize everything!

Please forward this to the systems admin in your life that wants to take your big SQL Server, and cram it into a Virtual Machine against your wishes.

So you’ve got your kick ass virtualization project moving along nicely, but you’ve hit a snag.  The DBAs are fighting back saying that they don’t want to virtualize the SQL Servers.  You hear them, but you know better right?  You built the hardware, you know what it can do.  The SQL Server doesn’t really need all that horse power. Continued »

SQL Server gets an ANSI compliant unique index … sort of

If you work with any of the other big database platforms you’ve probably noticed that SQL Server’s implementation of a unique index is “different” than the others.  Until now there hasn’t been a way to fix that without using a trigger.  Until now… Continued »

What color should my hair be for #sqlpass?

I posted a survey over on Security Fight Club to get some input from the SQL community.  I need to know what color I should die my hair for SQL PASS.  The survey will be open until shortly before PASS at which point I’ll announce the winning color.

Denny

PS: I would have posted it here directly but this isn’t my site, so I can’t add plugins to handle this sort of stuff here.

SQL Upgrades are a one way street.

That’s right folks.  Once you upgrade SQL Server versions there is no going back. Continued »

SQL 2005 SP3 Failing to install with password length error

When installing SQL Server 2005 SP3 you may get an error about the password being to short, and it may have nothing to do with your sa password.  It’s because the Install Scripts are trying to create a certificate with a password which doesn’t meet your domain’s password requirements.

If you get this error navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Upgrade folder.  Find the script for upgrading the SQL Agent.  Open the script and find the CREATE CERTIFICATE statement.  Make the password longer.  In my case I added 123456@ to the end of the password.  The password is in two different places in the script.

Then open Add/Remove Programs and select SQL Server 2005 and select Change.  Then run though the wizard to complete the installation of the service pack.

Denny

Join me on Oct 21st for a Clustering with iSCSI Presentation

All,

On October 21st, 2009 at 11am (Pacific) / 2pm (Eastern) I’m going to be presenting a free Webcast on how to setup a Windows 2008 Cluster when using iSCSI.  Additionally I’ll be going over some tuning tricks you can use to improve the performance of your iSCSI network. Continued »