My deadline is fast approaching.  It seems these days that editors have no patience.  On top of that there is this ever-shrinking deadline problem and the constantly shrinking lead-time to publication issue.

Oh wait, it’s only TSQL Tuesday.  But the end of TSQL Tuesday is fast approaching, and I am cutting it close once again.

This month we have been invited to this party by Rick Krueger (blog | twitter), and he wants us to talk about our hacks, kludges and wedgies.  We are calling them Rube Goldberg Machines for this month.  And a Rube Goldberg could be many different things in the SQL Server world.  Heck, it could be many different things in the IT world.

If you are unfamiliar with these types of devices, kludges, widgets, here is a good example from the folks at Mythbusters.

Super villain Monologue

My first inclination was to talk about a solution involving the use of SOAP calls in SSIS to perform batch credit card authorizations and then to do inventory control and process shipment orders.  That was a fun project.  It was also extremely successful compared to the previous solution.  We went from a nightly 12 hour process to finishing in 1-2 hours for twice the orders.

I then thought of a couple of solutions that involved the backup of a database on one server and then to restore the database on another server.  Minimal moving parts there as it only involved the backup, restore, and then to re-assign permissions (e.g. prod backup to QA environment, the permissions should probably be different).

Ka-POW

And then it hit me.  There is a pretty cool solution that involved multiple servers, multiple moving parts, and was somewhat useful.

Imagine having three servers that each perform a different function.  In our case, we will call them Server1, Server2, and Server3 – all of them are SQL Server.  Server1 is the primary production server.  Server2 is a mirror of Server1.  And Server3 is the DR server that is a secondary in logshipping.

Due to policy or previously established procedure, mirroring is stopped (maybe the deployment requires massive data changes and it causes issues with latency to the mirror.  Maybe the network link between servers is broken and you want to prevent excessive log growth – it’s up to you to find the excuse).  You already have a process for re-establishing mirroring and logshipping (yeah that got stopped too), but you want a 1-click approach.

Without going into extensive details, what worked in my scenario was to create linked servers between each of the three servers.  Then I could go and create a job relevant to each server (such as the steps listed below).  Each job would then execute a job step and then start the next job in the sequence via the linked server – if the job step was successful.  If it failed then it would fire off an alert to notify the appropriate people of where the failure occurred.

Here is a sample of the steps that could be executed.

1. stop logshipping on server1

2. backup db on server1

3. restore backup to server2

4. start mirror on server2

5. start mirror on server1

6. restore logs to server3

7. start logshipping on server1

Depending on the requirements, you may want to try something like this or do something completely different.  Your mileage may vary.  The point is, we have a little mouse trap here that triggers another mouse trap and then another until the task is complete.  Hopefully the setup will require minimal intervention, if any (that is a requirement of a Rube Goldberg machine – you start it but don’t touch it after that).

There you have it.  Nothing big and fancy. Just a short and sweet description of a possible avenue for some of those multi-server tasks you may have.