Database mirroring and hardware upgrades

One of the new features that arrived with SQL Server 2005 SP1 was Database Mirroring. This protects your database against physical disaster (but possibly not logical disaster) and is great for peace of mind. You don’t need to buy another license for the ‘mirror’ server and you can use SQL Server 2005 Express on your ‘witness’ box.

A huge upside of using mirroring is how easy it is when you need to do maintenance on your db server, or you have to upgrade the hardware or swap out the server entirely.

Lets consider a server swap out. You’ve bought a new server, nice dual proc quad core machine with a ton of RAM that you think will keep you going for a bit. It has SQL Server 2005 installed, with the latest service packs and hotfixes applied. The full backup of your old database (which is still in production) has been restored onto it, and you’ve just finished replaying all the available transaction logs to bring it almost up to sync with the production database. Now you’re pretty much ready to send it live.

Just stop the current mirroring session, and run through the mirroring wizard to reconfigure the setup so you have your new server as the ‘mirror’ server. A minute later, you hit the ‘start mirroring’ button on the databases you’ve configured for mirroring, and the databases sync up. Once they are synchronised (pretty quick unless they are *huge* databases) you just manually fail over the mirroring so that the new server becomes the new principal server.

Now you have your brand new server running your database in production, with no data loss and only seconds of downtime whilst the databases failed over. The old principal server is now your mirror – do what you want with it.


Temporary tables vs table variables

Query performance can be affected hugely by choice of temporary tables or table variables. Both have upsides and downsides. Faced with either type of table SQL Server will try to keep operations in memory, and both use the tempdb database.
The main differences are: temporary tables are written to the transaction log (so operations can be rolled back), you can create indexes on temporary tables and as such they often perform better when there are many records in the temp table and the query involves joins. Temporary tables however need more careful cleanup – the scope isn’t restricted to the procedure as with table variables. Finally, procedures with temporary tables may need to be recompiled frequently whereas procs with table variables can be pre-compiled.

If the recordsets for the tables are small, and the proc is executed very frequently then table variables might be the way to go. Try both flavours – certainly don’t assume that one is better than the other.

Comparing a couple of MySQL > MS SQL Server migrators

I have a mediumish sized MySQL database that needs porting to MS SQL Server 2005. The easiest thing to do I thought was check out the many migrator/converter apps there are out there. As they were fairly cheap at around $100 or so, I bought a couple: one from Intelligent Converters and the other from Spectral Core. The MySQL db is about 1GB so not that big, and hardware is a dual Xeon proc machine with 6GB RAM running 64bit Windows 2003 Server.

Tried the one from Intelligent Converters first – it went through the kind of screens you’d expect – pick a source, pick a destination, pick the tables you want to convert and then go. As this one started chugging through it displayed how many rows of which table it had managed to migrate. After 5 mins it was clear that it was going to take some time. Two days later, it had given up after migrating perhaps half the database.

So then the Spectral Core product. This was slightly more expensive, went for the enterprise version that made use of bulk copy so that we’d get better transfer speed. Very similar interface/process – then hit go. 2 mins and 15 seconds later, the entire database was copied perfectly. Happy with that.