Code Camp Presentation - Automating SQL Maintenance and Backups

I'm finally getting my presentation and database maintenance and backup utilities posted from Carolina Code Camp.  Thanks for being patient with me.  I'm posting the 1st version of my utilities, these are the ones I use everyday to manage my systems at work.  I'm also working on refactoring them to cleanup my code and optimize the procedures and query plans as well as add new features and support for SQL 2008.  When I get the new stuff done I'll post it, so check back periodically.

Some of the basics on maintaining your database(s) are keeping the overall system running smoothly and running your maintenance in the correct order to get the most out of the maintenace.  Below is the order I would recommend maintenance.

1. History Cleanup
    - Remove old entries for backup history in MSDB
    - Removed old entries for database mail in MSDB
    - Cycle Error Logs for SQL and SQL Agent

2. Transaction Log backup before maintenance (Full Recovery Model - empties log file on database)
3. Check Database Integrity (Database, Allocation, Catalog, Table, Filegroup)
4. Defragment Database Files (Data, Log, file_id, file_name)
5. Rebuild HEAP on tables w/o Clustered Index (it is better to have a clustered index on every table)
6. Rebuild Clustered Indexes
7. Rebuild Non-Clustered Indexes
8. Update Usage (* only needed after upgrading from 2000 or on a 2000 database)
9. Update Statistics (All, Column, Index)
10. Transaction Log backup (Full Recovery Model - before backup to empty log file)
11. Full Database Backup

To understand the reasons for doing maintenance on the database, take a look at my presentation.  I have included a section on the Database File Architecture.  As the database is used and records are added, deleted and updated, it causes the database and indexes to fragment just as the hard drive on your computer does.  The data files may seem like just a container that holds data but they are far more complex than that and contain a lot of parts that need maintaining.  If you have any questions, feel free to shoot me an email and I'll do my best to help you.

A link to my presentation, utilities and documentation is below.  Here is a list of the utilities I have in my database copy and this version is for SQL 2005, I have another version of the database for SQL 2000 (email me if you need it).  I put them in a database because it was easier to deploy them that way and I wanted to keep them off master since in the future I plan on adding more functionality.

CHECKDB
DEFRAGDB
REINDEX
STATISTICS
BACKUPS
Backup History Cleanup

Database Mail Cleanup
Clear Proc Cache Adhoc
Alert Broken Log Chain

 *** Update defaults for procedures to suit your environment ***

Note:
As with any code that you are not familiar with, review the source code before using it.  The last thing you would want to do is execute something you never checked against your production environment and find out that somebody slipped something like a DROP DATABASE command in there.

This code has been tested and is supplied to you without warranty and as is.

Known issues:
 - Case-sensitive collation: there are minor bugs related to my variable names and table names that I haven't fixed in this version that will only cause an issue on a case-sensitive collation.

- Alter Index (Rebuild Indexes Online), Enterprise version of SQL 2005: I need to correct my logic for online reindexing, it does not currently support online reindexing on Enterprise version.





There are also some other individuals who have created some great utilities for maintenance as well.  My utilities work well for me but they are not the only solution out there, go check out Ola Hallagren and Tara Kizer's maintenance solutions.  The funny thing is that we have all written our utitlies independently and there are strong similarities between them, I guess it comes down to there are only so many ways to boil water.

Ola Hallagren
http://ola.hallengren.com/

Tara Kizer
http://weblogs.sqlteam.com/tarad/Default.aspx

SQL Database Documentation Tool

Database documentation is one of those fun things that everyone loves to do, ok not really. It is usually difficult and hard to do and if there are changes to a table schema or stored procedure, you can't really query the database to ask it what was changed.

Along comes Red Gate with their SQL Doc utility to make our lives easier in the database world anyway. I gave this a try earlier this year and documented every database on every server. The cool part is that it'll show you all the dependencies between objects and generate scripts for all the objects to re-create them. You can output the documentation in HTML format, Word Document format, or my favorite a CHM help file. The CHM help file seemed to be the most useful to me as I could easily search on every item in the database on the server. The process doesn't take an extraordinary amount of time to compile either. So when you first design a database, you can create a documentation snapshot. Then you can snapshot the documentation again after changes or whenever you need to troubleshoot or want to see the latest info.

It would be really cool if the utility could just tell you the changes from the original configuration and who changed it, but that will require some type of auditing. However, you could use a document differencing utility and compare version of documentation to find the deltas.

Go check out the utility and see what you think. Red Gate offers a 14 day trial to test drive it.
http://www.red-gate.com/products/SQL_Doc/index.htm

Why you should specify column names in your T-SQL SELECT statements

This is one of those things that irks me and I know the reasons to explicitly specify the column names in your select statements. One is to limit the data to exactly what you need and to also speed up compilation of the query because it has to be ultimately returned to columns on the backend of the database engine. Then if you end up with schema changes, it just causes all sorts of problems. However, I just saw a blog post from Aaron Bertrand on this subject and he does a much better job of explaining it in a very straightforward manner, go check it out.

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx

SQL Server Collation

Server and Database Collation on SQL Server is something that you never really give much thought until you run into a problem with it. I just happened to run into a problem with it today that caused me some pain and pointed out some minor flaws in my t-sql code. The problem turned out to be a case-sensitivity problem because of the server collation. Someone had installed SQL Server 2000 and set the collation to be case-sensitive instead of the default of case-insensitive. On SQL Server 2000, the collation can only be set for the server during installation. So unless you want to re-install SQL you're stuck with it. On SQL 2005, you can change it without re-installing the server but it's not much easier. There are plenty of blogs out there explaining how to do it. The real problem seems to be if you've designed a database with the wrong collation and want to go back and change it. I haven't tried it but the basic idea I got was that you script out the database objects and and re-create them and then import all your data into the new structure...but I haven't tried this nor do I want to. An easier solution is to take a look at the collation before you build the server and all the objects in the database(s).

Here is what I'm rambling about, lets look at the collations and explain what they mean. There are a ton of them, but I'm going to cover the default and its cousin that caused me trouble today. By default SQL Server, at least the English version, uses "SQL_Latin1_General_CP1_CI_AS" as the collation for the server and that is inherited by all the database which are then created on that server unless you specify otherwise. The "CI" in there means "case-insensitive", the other version has a "CS" for "case-sensitive" or "SQL_Latin1_General_CP1_CS_AS".

So what is the big deal over case-insensitive and case-sensitive you ask? I'm not 100% sure why you would want case-sensitive on your database server. On the upside, all of your variables, columns names, text in your where clauses have to be the exact case of whatever you're querying. On the downside, it can make for some tricky debugging later on and drive you crazy trying to figure it out. Here is why, you can could have multiple variables...yes multiple variables with the same name but variations of case which are unique variables. So @DbName, @dbname and @dbName would all be different variables. This is what caused my problem today, I had some stored procedures that I wrote and I guess I forgot the exact case of my variable throughout the code where I used it 20 something times. I declared the variable as @DbName varchar(50) and then called it in one place as @DbName and another as @dbname and I got back an error saying that I needed to declare my variable. The same thing had happened with column names and trying to query a database name. I fixed the case-sensitivity issues and got everything working.

What is the point of this? Choose your collation carefully when building your SQL Server and designing your databases. Case-sensitive collation will force every query, variable, columns, etc to be the exact case of whatever data you're dealing with and allow multiple variations of the same variables to be specified. Case-insensitive will allow variables and queries to be written without regard to the case of the query, columns and variables as well as ensuring that a variable name can only be declared and used once.

Large Memory Support in Windows Server 2003 x86

Have you ever built a x86(32-bit) Server with lets say 2GB of RAM and then later added more RAM bringing the total RAM to 3GB or 4GB? And then sat there scratching your head trying to figure out why an application such as SQL Server cannot utilize the memory beyond 2GB? I had this very issue happen the other week with Windows Server 2003 R2 SP2 (x86) and SQL Server 2005 Enterprise (x86) installed on a test server with 3GB RAM. I could not get SQL Server to allocate more than 1.7GB RAM and then remember the /PAE and /3GB switches in the Boot.ini file. I had completely forgotten about the settings since I've been working on mostly x64 systems in recent years. Once I added the switches and rebooted the system, SQL Server was able to allocate more memory and the problem was resolved.

Here is what's going on, Windows is not recognizing all of the memory in the system and it is unable to address it. Your next question should be does the hardware architecture support x64(64-bit), this may also be referred to as EMT64. 64-bit(x64) does not have the same limitations on memory as 32-bit(x86) and can address much more memory. Your system or software might not support x64, if that's the case then you're stuck using x86. If the system has an Intel processor, download the Processor Identification Utility from Intel to find the details of your processor and what it supports:
http://www.intel.com/support/processors/tools/piu/
I also have a blog post with information on the Intel Processor Utilities:
Intel Processor Identification Utility

Here are the steps to fix the memory issue so Windows Server 2003 can address the memory. Physical Address Extensions need to be enabled for the system, this is done by modifying the Boot.ini file. There are two switches that need to be present, maybe one depending on your system configuration. The switches are /PAE and /3GB. If the system has more than 16GB of RAM then remove the /3GB switches or the system won't see anything beyond 16GB. Also, these switches only apply to x86(32-bit) server operating systems. The Boot.ini can be edited by going to Control Panel > System > Advanced tab and clicking on the Settings button under Startup and Recovery then click Edit to change the Boot.ini settings. Be careful and double-check what you modify or the system might not boot. If the system doesn't boot after the switches are added, then boot to Safe Mode and change the settings back.

The following is an example of a Boot.ini file where the PAE and 3GB switches ha
ve been added:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /3GB

For more information, read the Microsoft KB article on the subject:
http://support.microsoft.com/kb/283037


If you're using Windows Server 2000 and SQL Server 2000, please refer to these Microsoft KB article for assistance.
How to configure SQL Server to use more than 2 GB of physical memory:
http://support.microsoft.com/kb/274750

SQL Server only uses 2 GB of memory even though the AWE option is enabled:
http://support.microsoft.com/kb/811891/

Adding Active Directory Users to SQL Server behind a Firewall

I ran into an issue the other day that I thought others might be interested in knowing. I built a new system and turned on Windows Firewall, then discovered that I couldn't add users from Active Directory to SQL Server. This server happens to be SQL Server 2005 w/SP3 on Windows Server 2003 R2 w/SP2, however this is not the important part. I did a little research on the ports used by SQL and Windows Server and found a bunch of information which is in a previous post, "Network Ports used by Microsoft Server Products". I ultimately decided to try a few key ports and determined that I needed to enable Port 445 for TCP traffic. Enabling TCP on Port 445 resolved my issue and I was able to add users from Active Directory once again. The port is listed under a default setting in the Windows Firewall Exceptions, it is under "File and Printer Sharing". Click on "File and Printer Sharing" and choose "Edit", then Enable Port 445. This should resolve the issue with adding Active Directory users to SQL Server.