SQL Server BOL - Getting Very Decent

The last month I have been working on backup and restore operations and how to automate the process - to be used in an external job scheduler.  And I have the blessing at my new position to have a heavily used test environment that does a lot of backups, restores, and copies from production.  This has enabled me to experience recovery path forks pretty well.  After a half day of working out what all the fields in backupset stand for and how to determine the proper recovery path for any given fork I found a link that took me to the 2005 documentation on recovery forks.  It provided me with a little new information that I then worked on for a day.

The I switched over to the 2008 R2 documentation and it had new, useful information.  I must say that it validated a lot of what I had determined and pointed out what a few more fields were there for.  And in the process answered the question that I was actually at.

Hooray for the continuous improvement in the BOL @ Microsoft!

And incidentally today I stumbled across a single sentence in BOL that answered an open question of mine.  That being "how do I repopulate the backup/restore tables in msdb from old backups when they get restored from tape?"   (RESTORE VERIFYONLY FROM <insert_file_name> WITH LOADHISTORY)

CodePlex Project centraldba

A couple days ago I launched a project on Codeplex to host my collaborative work for database management. Instead of some sporadic blogs I thought I would now create a set of projects that implement the work for others to use and also share new ideas into. (Actually, I recently switched jobs and the new company allows code sharing - and I needed to rebuild everything at my new job too.)

This project currently has the core project available for download. This core project is a set of tables, views, functions, and stored procedures - including a CLR object - that support a set of scheduled jobs. Coming in the near future will be some documentation on the use of the core project, including some scripts for setting up the scheduled jobs in a standalone instance or in a MSX/TSX environment.

Also there will be a project for simplifying database restores on the original database and server or to a different database and/or server. And another project or set of instructions for setting up the data collection utility for SQL Server 2008 - and if I determine how to make it backport - 2005 as well. There will also be rdl files to publish to a SSRS server to run off the MDW instead of using SSMS (have to share all that information with our users).

And it wouldn't be an automation utility if it didn't perform some level of analysis and alerting based on the data collected by the MDW. Nor would it be automation if some corrective actions weren't made for you to save you getting middle of the night alerts.

Of course there will be the reuse of ideas as much as possible - with credit being given where due (and I might need to add some more credits in the core project yet).

Any DBAs who want to get involved are invited to come join the project on Codeplex. I know that what is already in my head is useful to others. But I also know that there is plenty that I haven't even thought about yet. My goal with this project is to bring many of these ideas into a single place that is a functioning project model to implement. I have found many ideas in internet readings, but very little in the way of a fully functional system available at no charge.

So I hope that the centraldba project is found useful by others in the world - and not just by me. But it will at minimum be useful to me and my new employer - and has been already.

New Blog to Follow

I had the opportunity to speak with a SQL Server DBA today that I was glad to meet.  Just from the way she talked it was obvious that I was speaking with someone who knew what she was talking about.  After lunch one of my co-workers found she had her own blog and forwarded it.  I have added it to the list to the right so I would not loose this one.

Nice to have met you today Erin.

Installing SQL Server 2008 R2 Cumulative Update from a Network Share

Note: this bug only exists if the machine you are running the update on has .Net 4 installed.

It has been the process at my work location that we store all install media and patches on a network share - as is probably a common practice (much easier than CDs/DVDs).  And this practice has worked well for us.  But to expound just a little, here is the standard practice of how we prep that install share - using SQL Server 2008 R2 Cumulative Update 6.

1. Create a directory to hold the patches (<install share>\SQL2008R2\patches\rtm\cum07\x64).
   
2. Download the SharePoint and Native Client updates to this new directory.
   
3. Under this directory create a sub-directory (cum07).
   
4. Download the main updater to this sub-directory.
   
5. Using 7-Zip, extract the three downloads from their EXE containers.
   
6. The main updater extracted another installer.  Extract this installer using 7-Zip.

And now the install share has been prepped.  Prior to Cumulative Update 6, this is all that needed to happen (given - I don't remember which update prior to 6 I had previously attempted; but I believe it was CU5).  Starting with CU6, the main installer fails with a very non-helpful error dialog (asking if you want to debug).
After some searching it appears that this is a new feature in the installer that causes install from a network share to fail.  But this can be fixed.  There are two files that need to be edited to allow installing from a network share.

1. <main installer directory>\setup.exe.config
   
2. <main installer directory>\x64\setup100.exe.config
3. <main installer directory>\x64\fixsqlregistrykey_x64.exe.config
4. <main installer directory>\x64\fixsqlregistrykey_ia64.exe.config
5. <main installer directory>\x64\fixsqlregistrykey_x86.exe.config
6. <main installer directory>\x64\landingpage.exe.config

Inside both of these files there is this following snippet.

<runtime>

  <legacyCasPolicy enabled="true" />

</runtime>

This section should be changed to the following, and installing from a network share will work.  I do not know what other impact this has - but have not noticed any negative side affects yet.

<runtime>

  <!--<legacyCasPolicy enabled="true" />-->

  <!-- The following two changes allow for install from network drives -->

  <legacyCasPolicy enabled="false" />

  <loadFromRemoteSources enabled="true" />

</runtime>

legacyCasPolicy: http://msdn.microsoft.com/library/ee191568.aspx
loadFromRemoteSources: http://msdn.microsoft.com/library/dd409252.aspx

Discussions
http://connect.microsoft.com/SQLServer/feedback/details/573043/sql-server-2008-r2-cumulative-updates-wont-install-with-dotnet-framework-4-0-30319
http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/3bc7cebb-2264-4e51-878b-0cce193c2780

Reading and Writing Instance Specific Registry Values

In my last post I gave sample code that used the procedure xp_instance_regwrite.  I thought that this deserved a brief explanation.  There are a set of extended stored procedures that Microsoft has provided for working with an instance's registry values.  These functions will go to the proper location of the registry for the instance you are querying from within. 

Working with the previous example:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'MsxEncryptChannelOptions', REG_DWORD, 0

For a default instance, this registry path equates to:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\[instance_id]\SQLServerAgent

The benefit of this is that one does not need to read a registry key to determine the proper place in the registry to find values. Which is what I used to do. I used to first read the following key to get the [instance_id] and then go in and read values. The xp_instance_* functions remove the need for these extra steps.

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\[instance_name]

For a full list of the xp_instance_* functions, please see: http://codebetter.com/raymondlewallen/2005/09/12/manipulating-the-registry-using-t-sql/.

SQL Server: "Automating Administration Across an Enterprise"

Automating administration in a SQL Server environment has been a goal of mine for a long time.  For years I used custom scripts that were then published separately to my SQL Servers to have a baseline of scripts.  For scheduled jobs, I would add it on one server and then script it to add to the other servers.  I had read about the concept of master / target servers and thought "boy would that be nice" but never came up with the time to implement.  And then there was, of course, the SSL cert for data encryption between servers - which alone has never been supported in an environment that I've been in.

Multiserver Administration:  http://msdn.microsoft.com/library/ms180992(v=SQL.90).aspx

Then at the start of 2010 I was in the process of building a new central cluster environment and thought - "now is the time to do this."  The first thing to overcome was the SSL cert.  I was still in an environment that would not support the encryption.  I came across an article that explained how to turn off the encryption requirements.  It boiled down to two simple commands and an instance restart.  I was all set to go.

-- Enable MSX Master Server Unencrypted Settings

--- @ref: http://msdn.microsoft.com/en-us/library/ms365379(v=SQL.90).aspx

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'MsxEncryptChannelOptions', REG_DWORD, 0

---  @ref: http://msdn.microsoft.com/en-us/library/ms175104(v=sql.90).aspx

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'AllowDownloadedJobsToMatchProxyName', REG_DWORD, 1

With this done, everything works just as described in the BOL.  I am able to register my jobs inside of a single server and then enlist all of my other servers to retrieve their jobs from that master.  Changes to my jobs: make it on the master server and it automatically propogates out to all of the target servers.

How To Instructions:  http://msdn.microsoft.com/library/ms189880.aspx