Welcome back and happy new year! Today I wanted to share with you my experience with SQL Server 2005 Reporting Services on a 4 node cluster.

The other day we had failed over one of the nodes to test functionality to see if the Reporting Services databases failed over as well. After the fail-over the Reporting Services databases did fail-over however, Reporting Services could not be found. After researching, we discovered that the databases for Reporting Services for SQL Server 2005 are cluster aware just not the service itself. In order for Reporting Services to work properly in a clustered environment… the Reporting Services needs to be installed on every node in the cluster. So when a fail-over occurs the Reporting Services can be found on the new node and connect to the databases. So I like to call this a “poor mans” cluster aware service.

If you have any experience with this please post and share your experience!

Today I wanted to share with you my “painful” experience with having SQL Server Integration Services (SSIS) installed on a cluster. I just got done installing and configuring a 4 node (3 active 1 passive) cluster. It has Windows 2003 and SQL Server 2005 Enterprise edition installed on it. I got the database engine, reporting services, analysis services and integration services installed and configured properly.

After installing and configuring everything we did a fail-over test. Everything had failed over except for SSIS. Come to find out that SSIS IS NOT cluster aware. You can view the article here that I came across on Microsoft’s website. There are work arounds or “hacks” as I like to call them. Because I am supporting a large production environment and need to minimize downtime as much as possible, we created a separate stand-alone SSIS server. From the SQL Server agent jobs on the production SQL Servers we do RPCs to the SSIS server that kicks off the packages. This has worked great! If anyone has succeeded in the “work arounds” or the “hacks” let us know!

Watch the latest videos on YouTube.com

Today I wanted to share the different ways of maintaining a Disaster Recovery (DR) system. One of the most important functions that a Database Administrator has… is to design and implement a DR plan for a production environment. Planning for a disaster has the following aspects:
1. Creating a backup plan for all databases
2. Setting up a “warm” backup server with the databases that are critical
3. Testing the backups at regular intervals
4. Circulating backups for off-site storage
5. Documenting the backup recovery system and to train co-workers in DR
procedures

Here are the different methods for maintaining a DR system:
1. SQL Server Cluster (SQL Server 2000 and 2005)
2. Warm Standby Server (SQL Server 2000 and 2005)
3. This can be maintained by log shipping, RPCs (job based), Transaction
Replication or through DTS or SSIS.

DR enhancements in SQL Server 2005
1. Database Mirroring
2. Peer To Peer Replication

I suggest to read up on each of these methods and decide which DR system fits your back-end infrastructure.