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

Code Camp Presentation info coming soon...

To all who saw my presentation at Code Camp on Automating SQL Maintenance and Backups today or want to see it. I will try to get the presentation and code posted within the next couple of days, just need to organize it and write up some additional info for you. Check back soon...

Thanks,

Jeff

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.

Carolina Code Camp

Carolina Code Camp is coming up quickly! It will be on October 10th, 2009 at CPCC - Levine Campus in Matthews, NC. More information can be found on the website:
http://codecamp.developersguild.org/

We are still accepting speakers for the Carolina Code Camp. We will have the traditional Code Camp stuff for developers and we also have a SQL Server Track with a focus on the topics that interest the development community.

Call for SPEAKERS...If you've been wanting to speak or looking for a venue to speak at about .NET, SQL Server, ALT.NET, Project Planning, Mobile App Dev, iPhone App Dev and anything else on technology that you think an audience would like to hear about. Now is your opportunity to speak to your peers.

If you're interested in attending Carolina Code Camp, the event is free. Just stay tuned to the website and registration will be opening soon...
http://codecamp.developersguild.org/

Jeff

Overview of Hyper-V on Windows Server 2008

Last week I was at the Charlotte Alt.Net user group and gave a short presentation on Windows Server 2008 Hyper-V. My presentation was an overview on the topic and gives some information on the features, how to install it, and how to create your first Virtual Machine. Before you use Hyper-V I also show you how to tell if your hardware will support it. Here is a link to the presentation:

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.

Intel Processor Identification Utility

I found a great utility from Intel that will tell you everything you wanted to know about that little silicon chip in your system that makes it run. Intel has two Processor Identification Utilities, one is for the new processor architecture and the other is for the old processor architecture. The general rule is Pentium IV and later processors use the Processor Identification Utility and processors older than Pentium IV will use the Processor Frequency ID Utility. To find out what utility to use, go to this page on Intel's website:
http://www.intel.com/support/processors/tools/piu/sb/CS-015472.htm

Intel Processor Identification Utility:
http://www.intel.com/support/processors/tools/piu/

Intel Processor Frequency ID Utility:
http://www.intel.com/support/processors/tools/frequencyid/

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/

Carolina Mega Code Camp Announced...

Save the Date! The Carolina Mega Code Camp has been announced to be held on Saturday, October 10, 2009 at the Levine Campus of CPCC in Matthews, NC. This event is also known as Charlotte Code Camp.

"Developing for Dollars, our year-long focus on providing you with the skills to create successful applications and connecting you with the right organizations to make money from your work, continues. We have invited every .NET user group in the Carolinas to participate in this Code Camp. Never before have so many groups come together to present one single event. So mark your calendar and plan to join us on Saturday, October 10 at the Levine Campus of CPCC for the Carolina Mega Code Camp! More event details coming soon."

See the Charlotte Developers Guild website for more information:
http://www.developersguild.org

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.

Network Ports used by Microsoft Server Products

Here is some helpful information I stumbled upon this morning on the ports used for various Microsoft Server Products. At one time or another we've all tried to figure out the various ports used by different services to communicate between systems. Here is a list of some of the more common and some not so common ports used. Scroll to the bottom of the page to find a full list of the pieces mentioned in the support article.

http://www.microsoft.com/smallbusiness/support/articles/ref_net_ports_ms_prod.mspx

New New Blog...

I'm finally getting my new blog setup after urging from friends and colleagues to start blogging again. I'll have some new posts coming up soon on SQL Server optimization tweaks and some maintenance stuff I've been working on.

Keep an eye out for the Charlotte Mega Code Camp, it will be on October 10, 2009 and I'm working on a presentation or two to give at that. The event will be at the CPCC Levine Campus in Matthews, NC. When I finalize what I'll be speaking about I'll post some more info. Stay tuned for details and check out the Charlotte Enterprise Developers Guild website for info on the event. Charlotte Enterprise Developers Guild