SQL House Keeping

I made the mistake of creating my SQL server virtual disks (database and log disks, both comprised of RAID 1 arrays) so that they encompassed the entire datastore. I didn't think this would be much of a problem but if you use snapshots in any way, shape or form then you will run into problems because the snapshots must (I think) go on the same datastore. We use vSphere Data Protection and so when it was doing a backup the SQL server floundered due to lack of disk space.

So apparently you can't shrink a virtual disk, or at least I don't know how to.

I had to move all the databases and logs onto a third disk and then remove and reconfigure the log and database disks and then move all the data back onto the newly resized disks. Here is how:

For each user database run this query for each database, index and log:

ALTER DATABASE SUSDB MODIFY FILE ( NAME = SUSDB , FILENAME = 'H:\Data\SUSDB.mdf' );
ALTER DATABASE SUSDB MODIFY FILE ( NAME = SUSDB_log , FILENAME = 'H:\Logs\SUSDB_log.ldf' );

The "name" field maps to the logical name of the file. The "Filename" should map to where you want to move the file.

After running this query, stop the SQL Server service and then manually move the files. Restart the service (and the agent if necessary) and check that the DB starts and then the files are where they should be. This command also helps to verify this:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SUSDB');

IMPORTANT: It appears that the destination folder must have the following user account present in its ACL with full rights:

SQLServerMSSQLUser$fwsql$MSSQLSERVER

The Master DB is slightly different. You must edit the start up parameters in the Configuration Manager to the new locations. Config Manager > Services > MSSQLServer Properties > Advanced > Startup Parameters.

Within the parameters "-d" is the Master DB, "-e" is the error log and "-l" is the log.

Once you have changed these values, stop the service and move the files and then restart the service. Make sure the folder that you move it to has that unwieldy SQL account in its ACL.

The Model and the MSDB DBs are moved in the same way as the user databases.




Resources:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Talks of a "Startup Parameters" tab but there doesn't appear to be one.

Exchange 2003 ActiveSync

Creating a blog that acts as a reference to previous work is clearly not my forte.

We had an Exchange failure over the weekend. It wasn't actually an Exchange failure but a VMware ESXi 4.0 datastore that got out of control. The lesson learned was don't take snapshots unless you really have to and make sure you delete them as soon as possible. I already knew this but somebody, three years ago, took a snapshot called "test" and there it remained, undetected, consuming more and more of the disk until it was impossible to delete. It is impossible to delete because it needs to consolidate disks which are now too unwieldy to be merged on the remaining datastore. It is particularly difficult if you are using local storage and there's nowhere to go or grow.

So after a restore from tape everything was working again. Except ActiveSync.

So the environment consists of one front-end server and two back-end servers. In this scenario I am under the impression that as long as port 443 is open on your firewall the FE will catch all requests and pass them on to the BE servers. The default website config that the Exchange 2003 installation puts in place should just work. That is all the virtual directories (ExAdmin, Exchange, ExchWeb, Exchange-Server-ActiveSync, OMA and Public) will be set up with the correct security and functionality. The certificate should reside on the FE server which should also stipulate SSL.

In a scenario with no FE server AND SSL or forms-based authentication is enabled then you need to create a duplicate of the Exchange VD, assign it name (Microsoft seems to like "Exchange-OMA" but "Whatever-the-fuck-you-want" is ok too) and then point to it in a registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MasSync\Parameters\ExchangeVDir

This should be case-sensitive and contain a string such as "/exchange-oma" or "/Whatever-the-fuck-you-want".

But you don't need this registry entry if you have a FE server. Of course in the upside-down environment I was working in all servers have this entry, even the FE server. So in order to get AS working I did the same on the restored server and it started working again. God knows why, but no doubt I will be returning to this at some point.

 A side note to this is about resetting the VDs should they stop working. Here are the steps:

1. In IIS delete all the VDs - the ones named above. You can take a backup of the website first but I'm not sure how useful this is.
2. Go to c:\inetpub\adminscripts in a cmd and type "adsutil delete ds2mb".
3. Restart the System Attendant and see the VDs recreated.