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.

SQL Agent Error Log problem

Here is a quick fix to a small problem if you're running SQL Server 2000 and can't seem to get SQL Agent Error Logs to generate output to its log files. Go to SQL Agent and stop the SQL Agent service. Then go to the SQL Agent properties and on the first tab under General, uncheck the "Write OEM File" and restart the SQL Agent service. For some reason, this stops the logging to the file. I have noticed that this is turned on for MSDE 2000 and it works fine, but it doesn't work on SQL Server 2000. See the screenshot below.

SQL Mail Registry Settings

If you're using SQL Mail on SQL Server 2000, at some point you may have run into this issue. You change the mail profile or maybe you remove the mail profile, and either the profile name won't change in SQL or you can't remove it. If there is no profile, then the option is disabled but still shows up. I have ran into an issue where SQL Agent will not start because it cannot find the mail profile associated with it. There is a work around for this...and it is in the registry. Also, there is a bug in SQL Management Studio for SQL 2005 that does not allow you to see the SQL Mail settings on a SQL 2000 system and will erase SQL Mail settings if you save any changes to the configuration through it.

Normally, you want to stay away from the Registry and avoid touching it as much as possible. But sometimes you have to change settings in it. Before we get to the solution, a quick primer on how SQL Mail works. SQL Mail has two separate pieces that work with SQL Server 2000, one is based on the SQL Service Account and the other is based on the SQL Agent Service Account. The service account being used has to be a Domain account with rights to an Exchange Account/Profile. SQL Server's SQL Mail is accessed through the system extended stored procedure, "xp_sendmail". SQL Server Agent's SQL Mail sends alerts and job notification through Operators. You can configure different profiles for SQL Server and SQL Server Agent or use the same profile for both. There is more information on this from Microsoft. SQL Mail is replaced by Database Mail in SQL Server 2005.

Configuring SQL Mail
http://msdn.microsoft.com/en-us/library/ms189620(SQL.90).aspx

SQL Server Agent Mail
http://msdn.microsoft.com/en-us/library/ms188235(SQL.90).aspx


Caution: Edit the registry at your own risk and test any changes before changing your production system.

Now to resolve the issue and find the registry settings that contain the mail profiles for SQL Server and SQL Agent. These steps are for SQL Server 2000, SQL Server 2005 has similar settings but they are under different registry paths and names. Here are the steps:

1) Go to start, run and type regedt32 to open the registry editor.

2) Navigate to the settings for SQL Server under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > MSSQLServer"

3) Locate the String Value for "MailAccountName" and change the value to the new profile name or remove the value if the profile no longer exists.

4) Navigate to the settings for SQL Server Agent under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > SQLServerAgent"

5) Locate the String Value for "EmailProfile" and change the value to the new profile name or remove the value if the profile no longer exists.

SQL Server Agent Jobs managed on the web and least privilege

I ran into an interesting scenario the other day at work with a SQL Server 2000 system. A developer was updating a web page that allowed business users to monitor/execute a SQL Agent Job or more to the point monitor and execute multiple jobs. This web page had been working fine for some time and they stopped working. As it turns out the permissions were changed on the account after the 3rd Party application was upgraded and the old permissions were reset. The root cause of the problem was a permissions change and the old permissions were set too high and gave too much privilege to the account. As a DBA, this frustrates me but we'll avoid that and get to the point of how to resolve the issue.

Here is the scenario...multiple SQL Agent Jobs being managed via an Intranet Web Page by the business end-users. The web page is connecting as a privileged user with sysadmin rights, this is never recommended. So how do we fix this?

The SQL Agent Jobs are using a mix of T-SQL and Operating System (CmdExec), the CmdExec requires SysAdmin rights to run. There is another option and that is to use a SQL Agent Proxy Account for the job, this account will not have SysAdmin rights and that means we can take away SysAdmin right from the other account. Now this creates another problem, now the web page throws an error saying it can't find the Jobs. The SQL Login is not a SysAdmin now and can't see the jobs, the problem is with the Owner of the jobs. The login must be the owner of the job in order to see or start/stop the job. The web page is also executing msdb.dbo.sp_help_job and the login needs to be granted the SELECT permissions on the msdb.dbo.sysjobhistory system table in order to view job history. All of this grants the least privilege to the login and helps to protect the server.

To summarize the above, here are the actions taken:
1) Create a SQL Agent Proxy Account
2) Run the job step(s) requiring higher privilege under the SQL Agent Proxy Account
3) Remove SysAdmin rights from the SQL Login
4) Grant least privilege to the SQL Login, the lowest rights required to access each database
5) Grant SELECT on msdb.dbo.sysjobhistory (System Table) to view Job History using msdb.dbo.sp_help_job (Stored Procedure)
6) Assign SQL Login as Owner of the SQL Agent Jobs being monitored and executed

For more information, check MSDN.

Permissions for sp_start_job:
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.


Permissions for xp_sqlagent_proxy_account:
http://msdn.microsoft.com/en-us/library/aa260700(SQL.80).aspx
Sets or retrieves the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. The proxy account is a Microsoft® Windows® account in whose security context the jobs or command prompt commands are run.

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/