Moving a SQL Server database to another server on a schedule – without using replication

Recently, I had the need to copy a set of databases from a dozen remote servers to a central server, restore them, and have it happen automatically, with no intervention from me at all. Replication wouldn’t work for the following reasons:

  1. Many tables didn’t have primary keys, so merge replication was out (even though this was only one-way replication)
  2. The size of the databases (28GB in one instance) and the quality/speed of the WAN removed the log shipping option
  3. There’s too much activity to consider any kind of live replication

Given our restrictions, we decided to go the following route. On the remote server, we set up a batch file that did the following:

  1. Use OSQL to back up the databases in question to a folder
  2. Run 7Zip from the command line to compress the backups into separate archives. For each auto-attaching later, each archive had the name we wanted it attached to the remote server with (for example, Site1ProdDB was backed up to Site1ProdDB.BAK, then compressed to Site1ProdDB.7z)
  3. Delete the BAK files
  4. Archives were renamed from *.7z to *.7zz (this is important – I’ll explain why in the server part)
  5. Scripted FTP using Windows command line FTP tool to a folder on our central collection server
  6. Once the FTP was complete, rename the archives on the remote server back from *.7zz to *.7z
  7. Delete the local *.7zz files

That’s it for the client – the BAT file was scheduled as a SQL Agent job so that we could kick it off remotely from any site we wanted, or so we could set them up on a schedule. Then, we put a BAT file on the server that did the following:

  1. Check folder for files that match *.7z
  2. For each one found, do the following:
    1. Extract it to a “Staging” folder
    2. Delete the 7z file for that archive
    3. Use OSQL to restore the file from the command line
    4. Use OSQL to run a script that changes the DB owner, adds some user permissions, and generally does some housework on the database
    5. Use an SMTP tool to send a email notice that the backup has been restored
  3. Repeat step 2 for every .7z file in the folder
  4. As a second step in the SQL Agent job, run “MoveLog.bat” (included below) to finish rotating the logs – it ensures that only logs with meaningful information are kept

The server BAT process can run as often as desired – in our case, we run it every 30 minutes, so the backup will be picked up and restored as soon as it’s available. That’s where the rename from the client side comes into play: If the files were named Database.7z, then the server process would attempt to pick them up while they’re being uploaded via FTP, and shenanigans would ensue. By renaming them when they’re done uploading, they become immediately available for restoring on the server side.

As I said before, I scheduled both the client (source) and the server (restore/destination) process as SQL Agent jobs – the Windows scheduler is too cumbersome to work with remotely, and kicking them off on demand was a pain. With the SQL Agent, they can be started on demand, and then I get an email notification as soon as they’ve been successfully restored.

I’ve attached the files below, and I welcome any feedback that you have or any improvements that can be made – I’m happy to give you credit and post a new version here. Specifically, I’m interested in any feedback about how to make this process more dynamic – I know BAT scripting supports FOR EACH and wildcards, but I was unable to make them work properly with OSQL, so I’d appreciate any input there. Enjoy!

Download the ZIP archive containing the files for this post

Expand a disk partition under Windows XP or Server 2003

Vista/Server 2008 include support for expanding hard disk partitions in the Disk Management MMC snap-in, but XP/2003 support it as well (if you’re not afraid of the command line). To expand a partition:

  1. Open a command window
  2. Type “DISKPART” and press Enter to run the partition manager
  3. “LIST DISK” and press enter to show general disk information, including unused space
  4. “LIST VOLUME” to show the volumes. Make note of the volume you’d like to modify
  5. “SELECT VOLUME 0”, where “0” is actually the volume number you’re expanding
  6. “EXTEND” to expand the partition into all available space
  7. “EXIT” to leave DISKPART

There you go – enjoy your newly expanded partition, with no reboot necessary!

WARNING: Messing with your partitions can cause serious problems, including losing the ability to boot Windows or permanently deleting data. Only use this utility to if you know what you’re doing! You’ve been warned!

Encrypting data per-user in your .NET application without asking for a secret

When you encrypt something, you need some kind of secret to do so – a password that’s used to encrypt and decrypt your message, or a public/private key like a certificate. In order to get a secret, you usually have to ask the user for one – in most cases, a password – before they can decrypt their data and access it.

I had an application where I wanted to encrypt some database connection details between sessions. This is easy enough, but I wanted to do it without having to ask the user for a password – I just wanted it done transparently. I thought this meant storing a secret key somewhere, but there’s no safe place to keep a key -even if it’s embedded in your code, somebody who really wants it will always be able to get it. What I needed was a private place to store a key, where no other Windows user on the same computer would be able to access it.

That’s when I discovered the DPAPI – it’s been around since .NET 2.0, and it’s available through the System.Security.Cryptography namespace. What’s great about this cryptographic feature is that you can have Windows encrypt something with the same key it uses to encrypt files with EFS – a private user key that’s based on a hash of their Windows password. This means that you don’t need to ask the user for a secret – you’ve already got one handy, and Windows will prevent other users of the system from being able to decrypt your data. The downside of this is that if the user resets their password, you’re toast – if you’re only keep the data as a convenience (as I am), that’s no problem, but if you can’t afford to lose the data, you’ll need to ask the user for the secret instead of relying on this method.

This MSDN article that detailed its use, and after adding some code that made it easy to encrypt and decrypt strings, here’s what I ended up with:

Imports System
Imports System.Security.Cryptography

''' <summary>
''' Encrypts and Decrypts information using the current Windows user key
''' </summary>
''' <remarks></remarks>
Public Class DPAPI

    Private Shared EntropyString As String = "Some value I made up"

    ' Create byte array for additional entropy when using Protect/Unprotect method.
    Private Shared Function AdditionalEntropy() As Byte()
        Dim encoder As New System.Text.ASCIIEncoding
        Return encoder.GetBytes(EntropyString)
    End Function

#Region " Encrypt "

    Public Shared Function Protect(ByVal data() As Byte) As Byte()
        Try
            ' Encrypt the data using DataProtectionScope.CurrentUser. The result can be decrypted
            ' only by the same current user.
            Return ProtectedData.Protect(data, AdditionalEntropy, DataProtectionScope.CurrentUser)
        Catch e As CryptographicException
            Console.WriteLine("Data was not encrypted. An error occurred.")
            Console.WriteLine(e.Message.ToString())
            Throw
        End Try
    End Function

    Public Shared Function ProtectString(ByVal data As String) As String

        Dim encoder As New System.Text.ASCIIEncoding
        Return Convert.ToBase64String(Protect(encoder.GetBytes(data)))

    End Function

#End Region

#Region " Decrypt "

    Public Shared Function Unprotect(ByVal data() As Byte) As Byte()
        Try
            'Decrypt the data using DataProtectionScope.CurrentUser.
            Return ProtectedData.Unprotect(data, AdditionalEntropy, DataProtectionScope.CurrentUser)
        Catch e As CryptographicException
            Console.WriteLine("Data was not decrypted. An error occurred.")
            Console.WriteLine(e.Message.ToString())
            Throw
        End Try

    End Function

    Public Shared Function UnprotectString(ByVal data As String) As String

        Dim encoder As New System.Text.ASCIIEncoding
        Dim b() As Byte = Convert.FromBase64String(data)
        Return encoder.GetString(Unprotect(b))

    End Function

#End Region

End Class

Download the code here

To use the class, just change the entropy value at the top (this is combined with the user’s key to create a new key used to encrypt your data), call Protect() or ProtectString() and pass it the required data, and you’re good to go! I added the ProtectString and UnprotectString because I was storing the values in the user’s app.config file, and needed an easy string representation.

If you use the class or have any questions, please let me know!

Accessing a clustered SQL Server instance without the instance name

When I clustered SQL Server 2005 the first time, it bothered me that I had to access each clustered instance using both the cluster DNS name and the instance name. If my SQL Cluster is called SQL-CLUSTER and the DNS alias of my first instance is SQL-INSTANCE1, I had to connect to SQL-INSTANCE1INSTANCE1. Since each instance has a dedicated name and IP address that uniquely identifies it, shouldn’t that be enough? Why is the instance name required?

It turns out it’s not! With a little tweaking, you can access the instance using just the SQL DNS name, without the instance name, and it works for as many SQL instances as you have on a cluster, not just one (you’ll need to repeat this process for each instance). Here’s how:

  1. On the active node for the instance you’re adjusting, open the “SQL Server Configuration Manager” from the start menu.
  2. Expand “SQL Server Network Configuration” and select the instance you want to edit.
  3. In the right window, right-click “TCP/IP” and select “Properties.”
  4. On the “Protocol” tab, ensure that “Listen All” is set to “Yes”
  5. On the “IP Addresses” tab, scroll all the way to the bottom, to the “IPAll” section.
  6. The “TCP port” box is blank by default – set it to 1433, the default port for SQL Server.
  7. Click “OK” – you’ll receive a message that you need to restart the SQL Service.
  8. Restart the SQL Service on that node.

Presto – done! You can now access this SQL Server instance using either “SQL-INSTANCE1\INSTANCE1” or just “SQL-INSTANCE”. It’s also worth noting that this network settings follows the instance between active nodes – if you failover the SQL instance to the other node, it should still be addressable the same way, and if you check the protocol settings, you’ll see that your change is active on that server now as well.

To me, it’s so much easier to not have to use the instance name when addressing the server, and since the service is responding on port 1433, the default port, this change should be compatible with every application that connects, and I haven’t had any problems at all.

If you try this and run into any problems, please leave a comment below.

UPDATE:

After playing with this a little more, it seems like it only works with the SQL Native Client. If you attempt to connect via the SQL Server OLEDB driver or the old SQL 2000 driver, you get the error that you’re not able to connect to the specified instance. Switching your connection method to the SQL Native Client (SQLNCLI) allows you to connect, assuming your application allows that. Has anybody else experienced this problem?

Scheduled Task “Could not start” when installing SQL Server on a Windows Cluster

I ran into this error while deploying SQL Server 2005 Enterprise to a two-node Windows Server 2003 cluster. The SQL Server installation checked all the prereqs with no problems, and as soon as it was time to actually do the installation, it paused for about 5 minutes, with the message “SQL Server Setup is preparing to make the requested configuration changes…” After a few minutes with no activity, the installation fails with the following error message:

I checked the “Scheduled Tasks” list on the passive node, and found the following:

Starting the task manually, even while setup was still running on the active node, had no effect. Also, the following entry appeared in the Log (Advanced Menu -> View Log):

“SQL Server Remote Setup .job” (setup.exe) 5/1/2009 11:29:45 AM ** ERROR **

Unable to start task.
The specific error is:
0x80070005: Access is denied.
Try using the Task page Browse button to locate the application.


SOLUTION:

It turns out it’s not an “Access denied” message at all! This occurs when you’re logged in to the desktop of the passive node while you’re doing the SQL installation. I had a remote desktop session open to both nodes, which caused this problem. Simply logging out of the passive node, then attempting the installation again from the active node, will allow setup to complete successfully.

Please, Microsoft: Add a pre-check to the setup process, or at the very least, give me a real error!

Resolving “STOP 0x0000007B INACCESSIBLE_BOOT_DEVICE” after you make changes to your hard drive configuration

I’d heard that, unlike previous versions of Windows, a Vista installation could be moved to a new computer without any problems, and would just re-detect all the hardware and reconfigure itself. So when I got my new computer, I imaged my boot partition to the new hard drive (it was much bigger than my old one), attempted to boot Vista, and got this error:

STOP: 0x0000007B (0x818B51B0, 0xC00000010, 0x00000000,0x00000000) INACCESSIBLE_BOOT_DEVICE

WTF? After much digging, it turns out that my new motherboard had AHCI enabled by default, whereas my old one didn’t. This is a SATA standard that supports Native Command Queuing (NCQ), special power management, and some other features. While Vista supports it out of the box, if no AHCI drives are connected when Vista is installed, it disables the drivers, and any attempt to switch to AHCI mode later will give you a blue screen at boot time. The general consensus online is that you have to reinstall Windows, but you do not!

To resolve the error:

    1. In the system BIOS, switch AHCI mode off. This will probably mean something like “Compatibility mode” for the drive – look for a setting that sounds like it does this, either for the controller or the drive itself. This will allow you to boot into Windows again
      1. a.  If you still can’t boot into Windows, you may need to rebuild the boot sector – not as scary as it sounds! Boot the the Vista install DVD, and when prompted, select “Repair installation”. After thinking for about 15 seconds, the install should say that it’s found the problem and corrected it, and you can reboot – Vista should come right up after that.
    2. Once you’re in Windows, load REGEDIT and navigate to [HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Msahci]
    3. Set the value “Start” to “0”. This will tell Windows to search for AHCI drives when it boots.

Reboot, and switch your hard drive back into AHCI mode when you do. Windows will boot, detect it, and install drivers. It will probably ask you to reboot again

  1. Resolved! Windows boots on your old Vista installation. Aside from this minor hiccup, Vista did indeed cooperate with my new hardware – it detected everything and came right up. It asked me to reactivate, but since it’s a corporate copy, that was no big deal.

Logon Failure: The user has not been granted the requested logon type at this computer

The printer I use to connected to a domain computer, but the computer I print from is not on the domain, so I ran into this ugly error when I tried to map the printer:

Logon Failure: The user has not been granted the requested logon type at this computer

This occurs because the workgroup computer is trying to pre-connect to the domain computer, and since it’s un-authenticated, it connects as “Guest”. Under normal circumstances, the guest account is disabled or, at least, it has been denied rights to connect over the network. Here’s how you remove this restriction:

  1. Download and install the Windows Resource Kit Tools (http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe). On Vista, you may receive an error that these tools are not compatible, buy you can ignore it – at least in this case, it doesn’t cause any problems, and currently. There’s no new version of the tools available.
  2. From the start menu, click “All Programs” -> “Windows Resource Kit Tools” -> “Command Shell” Run the following commands, in this order (and they’re case sensitive):
    1. net user guest /active:yes
    2. ntrights +r SeNetworkLogonRight -u Guest
    3. ntrights -r SeDenyNetworkLogonRight -u Guest

That’s it! You’ve enabled guest access, so non-domain computers will at least have the ability to connect, though they’ll be restricted to the permissions granted to the local “Guest” account, so you’re not opening up much of a security risk.

Join an Active Directory domain and keep your local profile intact

Recently, I had to join a number of computers in a small office to a domain, but the users all had local profiles that they wanted to keep. Things were a mess – some people’s usernames were the Last name, first initial (the username format I’d chosen for the domain), some were using their full names, and some were using the local administrator account. When I added these computers to the domain, their “domain user” would log in, and would create a new, empty profile. To avoid this took a few extra domain-joining steps, so I wanted to detail them here.

A note: On the computers that were using the local Administrator account as their main login, I had to create a new user and make them a local admin. I just called this user “Transition”, and deleted it once the process was over.

And now for the steps:

  1. Join the computer to your domain, and grant the new domain user local administrator rights before rebooting. Reboot, and log in using the new domain user. This will create a new, empty profile with that user’s domain login.
  2. While still logged in as the new domain user, take ownership of the old, local profile folder. To do this, right-click on the folder, select “Properties”, go to the “Security” tab, click “Advanced”, and then the “Owner” tab. You can set the owner to either the local admins group or the current user – set it to the current user. You must be a local administrator to take ownership (from step 1).
  3. Log out, and log in using either the local administrator account or the transition account you created.
  4. At this point, you can revoke local admin rights from the domain user if they won’t need them. They were only needed to take ownership in step 2.
  5. Open REGEDIT, select the “HKEY_Users” branch, and select “Load Hive…” from the file menu. In the user’s profile folder, there’s a hidden file called “NTUSER.DAT” – that’s the one you want to load. Make sure you’re loading the file from the old profile, not the one that was created in step 1. You can call it whatever you want when you load it – it doesn’t matter. Also, make sure you’ve made a backup of this file before you edit it.
  6. Right-click the user branch you just loaded, and click “Export…”. Export it as a REG file on your desktop.
  7. Open the registry file you just created in either Notepad or Wordpad (I find Wordpad faster for the this step, but it doesn’t really matter). Search for occurrences of “\OLDPROFILEFOLDERNAME\” and replace them with “\NEWPROFILEFOLDERNAME\”. I’ve generally found about 100 references, but it depends on the size of your registry. Also, make sure you convert 8.3 folder names as well – “\OLDPRO~1\” should become “\NEWPRO~1\”!
  8. Save the file after your found/replaced all the occurrences. Double-click the REG file to load it back into your registry (into the user’s hive). You’ll get a warning that not all data was loaded because some keys were in use – that’s fine.
  9. Since not all keys were imported, we’ll need to fix a few folders by hand. Select the user’s hive, and “Find” any occurrences of the old profile path, replacing them with the new path.
  10. With the main user hive folder selected, go to the “File” menu and select “Unload Hive”. The changes are saved automatically, which is why it’s important that you made a backup in step 5. Close REGEDIT.
  11. Rename the domain user’s profile folder to “Username.Empty” (since it’s essentially a blank profile), and rename the user’s local profile to “Username”, which matches the folder name of the profile that was created in step 1.
  12. Log out, and log in as your domain user, enjoying your old profile just as you left it!

This process can be repeated for as many users as you’d like to transition to the new profiles, and you should maintain every one of the settings for your programs. In fact, I’ve never had a program even realize something is afoot, though I’ve only done this on a half-dozen computers.

Please let me know if you have any feedback, and I’d be interested to know of any experiences you have trying this out!

Accessing System.DirectoryServices from SQL Server 2005

SQL Server 2005 allows for the integration of .NET assemblies into the databases so that they can be accessed from inside stored procedures and other database functions. Although this is a great new feature, I got hung up on a particularly cryptic error message when I tried to build an assembly and import it.

Since SQL Server makes it difficult to query active directory, and I wanted to build an AD-based authentication module for my database application, the best way to do that seemed to be to use this new feature. My assembly depended on System.DirectoryServices in order to access Active Directory, but that wouldn’t be a problem, since the .NET 2.0 framework is available from inside SQL Server 2005 (http://msdn2.microsoft.com/en-us/library/ms254506.aspx, provided you’ve enabled the feature), right? Well, sort of. As it turns out, SQL Server was rushed to RTM too quickly for all of the .NET 2.0 assemblies to be cleared as SAFE, so the ones that weren’t fully tested aren’t included by default. Fair enough – so it’s just a matter of importing System.DirectoryServices, and then importing my assembly that relies on it, right? Again, sort of.

System.DirectoryServices can be imported into SQL Server, but only as an UNSAFE assembly. This has all sorts of other security implications (which is a little ironic, since I was using it to verify user security), but I decided to use it anyway, since I figured that the UNSAFE tag was more of a formality than a real danger, and the assembly would be SAFE once more testing had been done. I imported System.DirectoryServices:

USE master
GO

CREATE ASYMMETRIC KEY asmKey_DirectoryServices
FROM EXECUTABLE FILE = 'c:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
GO

CREATE LOGIN asmLogin_DirectoryServices
FROM ASYMMETRIC KEY asmKey_DirectoryServices
GO

GRANT unsafe ASSEMBLY TO asmLogin_DirectoryServices
GO

That imports the System.DirectoryServices assembly as UNSAFE. Next, I imported my assembly as SAFE, since it was signed. The only problem was that when I called my assembly, which reached into System.DirectoryServices, I got an error (I’m calling clrIsMemberOfGroup in my assembly, SqlHelper):

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'clrIsMemberOfGroup':
System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

After 4 hours in the phone troubleshooting the issue with Microsoft, it turned out that it was VERY simple, and even vaguely alluded to in a knowledgebase document. In order to reach into the UNSAFE System.DirectoryServices assembly, I had to make my assembly UNSAFE as well. Since the UNSAFE assembly was running outside the bounds of what .NET considers “SAFE”, it could potentially return suspect results, and so anything that relies directly on those results couldn’t be considered “SAFE”, and had to be tagged as “UNSAFE”. It seems like I should be able to implement proper sanitizing code in my assembly so that I don’t inherently trust the results from my “UNSAFE” assembly, but SQL Server would have none of it. In order to reach into an UNSAFE assembly, I needed to flag my assembly as UNSAFE – simply placing my assembly import into a “Create key, create login, import assembly” setup like the one I used for System.DirectoryServices fixed the problem.

I suppose the question is really “Did that fix anything?” since all I really did was disable security on those assemblies. It’s really hard to throw a security assembly when you don’t do any sort of security checks. Well, at least I alleviated the symptoms, and now I’ll just wait for SP1 to (hopefully) add System.DirectoryServices (among other missing framework assemblies) to the assemblies accessible from inside the CLR access in SQL Server 2005. I suppose we’ll have to wait and see…