I recently stumbled across a great walk-through for clustering SQL Server on newer versions of Windows. It’s really thorough – everything from setting up iSCSI (in this case, to simulate a shared disk when it’s physically attached to one node – not ideal, but lets you test the walk-through), adding the required server roles, preparing the servers, and then a walkthrough of every screen in the SQL installation process. Thanks to the writer of this awesome blog!
We recently ran into an issue where linked server queries where sometimes taking significantly longer than they should have – queries that executed in under a second on the source server took two minutes when executed from a remote SQL Server over a linked server connection. We assumed it was something to do with our Windows Failover Cluster network connections, but it was happening on non-clustered servers as well.
After some digging, we discovered that it happened when the source server was Windows Server 2008 (or 2008 R2), regardless of what OS the remote linked server was running, and the issue wasn’t tied to any particular version of SQL Server. As it turns out, it related to everybody’s least favorite network setting, TCP Auto-Tuning – disabling it on the affected source servers resolved the problem immediately, and the queries returned instantly again.
To remedy the situation, follow these steps:
- On the source SQL Server (the one executing the query, not the linked server), open an elevated command prompt (Right click on CMD.EXE and click “Run as Administrator”)
- To disable TCP Auto-tuning, run the following command:
netsh int tcp set global autotuninglevel=disabled
- To disable Windows Scaling Heuristics (another feature that can speed up clients, but is no good for servers), run:
netsh int tcp set heuristics disabled
- This will automatically take effect after Windows has the chance to refresh some network communications – you can either wait about an hour, or, if you’d prefer it take effect immediately, restart the SQL Instance. A reboot isn’t necessary for this setting to take effect.
I’m not sure why these settings are on by default for servers – they seem to speed up internet browsing and other client-type activities (though there’s no shortage of complaints online about them grinding Windows Vista/7 clients to a halt as well), but can cause big problems on servers.
Ever try to debug something on a machine with many w3wp.exe worker processes and weren’t sure which one was yours? There’s a command line you can use to tell which is which:
In IIS 6 (Windows XP/2003), type:
It is located in C:\Windows\System32, which is almost always part of the PATH variable, so you can run the command from anywhere (like iisreset).
In IIS 7/7.5 (Windows Vista/7/2008/2008R2):
appcmd list wp
It is in C:\Windows\System32\Inetsrv, which is not in the PATH variable by default, so you can switch to that folder first and run the command.
If you’re running multiple app pools on the server with the same proxy user account, now you can tell which one owns the application you’re trying to debug!
When authenticating with a vendor using a custom webservice, the vendor requested that we use an x509 certificate with a 2048 byte key and an SHA256 hash (sometimes referred to as SHA2, though SHA2 actually refers to the group of hashes containing SHA256, 384, and 512). Since I’d used IIS to generate our certificate (IIS will only generate a certificate using an SHA1 hash), and it involved quite a bit of research to get a certificate with an SHA256 signature hash on it, I wanted to detail the steps here:
- First, download and install OpenSSL from Shining Light. The “Light” version of the package will do, since you’re only using basic functionality.
- Generate your Certificate request (CSR), specifying an SHA256 signature hash
openssl req -nodes -sha256 -newkey rsa:2048 -keyout C:\SomeFolder\PrivateKey.key -out C:\SomeFolder\CertificateRequest.csr
- You’ll be prompted for a few certificate fields, including your state, company name, computer name on your certificate, etc. Enter these as they come up.
- This will generate two files – PrivateKey.key (which contains the un-encrypted version of your private key – protect this file, as somebody who obtains it along with your signed public key can impersonate you), and CertificateRequest.csr (your certificate signing request, which is not sensative).
- Though this isn’t required, if you want to confirm what you’re entered in the CSR, you can view the details using another OpenSSL command line
openssl req -in C:\SomeFolder\CertificateRequest.csr -text -noout
- Now that you have your CSR, submit it to whatever signing authority you use – for us, it was Verisign, but there are any number of different CAs out there that can sign it.
- Once your CA has signed the CSR, you’ll get back either a binary p7b file (which we’ll called SignedKeyFromCA.p7b) containing your certificate signed public key (and, possibly, the certificate chain your CA used as well), or either a binary or base64 CER file containing just your certificate. Whatever you receive back, you’ll need to convert it to a Base64 CER (called SignedKeyFromCA.cer here), since that’s what OpenSSL expects.
- To combine your private key with the signed public key to create a certificate:
openssl pkcs12 -export -in c:\Temp\SignedKeyFromCA.cer -inkey c:\Temp\openssl.key -out SignedKeyPair.p12
- Since you’re exporting your private key in this file, you’ll be required to encrypt it with a password, which OpenSSL will prompt you for (twice).
- You’ve now got your signed key pair – SignedKeyPair.p12. You can either use this pkcs12 file in your code, or you can import in into your web server (assuming it supports SHA256 hashes). IIS7, for example, supports importing this certificate and using it for SSL, but just doesn’t support generating a SHA2 CSR in the first place.
Enjoy! If you have any issues, please feel free to post a comment and I’ll do my best to answer it!
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:
- Open a command window
- Type “DISKPART” and press Enter to run the partition manager
- “LIST DISK” and press enter to show general disk information, including unused space
- “LIST VOLUME” to show the volumes. Make note of the volume you’d like to modify
- “SELECT VOLUME 0”, where “0” is actually the volume number you’re expanding
- “EXTEND” to expand the partition into all available space
- “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!
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.
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!
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:
- 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.
- 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).
- Log out, and log in using either the local administrator account or the transition account you created.
- 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.
- 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.
- Right-click the user branch you just loaded, and click “Export…”. Export it as a REG file on your desktop.
- 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\”!
- 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.
- 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.
- 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.
- 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.
- 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!