Setting up a SQL rollover server

Posted on 2011-04-20
Last Modified: 2012-05-11
I am setting up a seperate sql server on a different network to act as a backup server in case the production sql server goes down.  I would like to know what is the best way to do this.  I was thinking about using ssis on the backup server and pull the data each night from the production sql server.  Is this the best way to go?  I also want to load the data the first time and then just update with changes.  Is this possible using ssis?
Question by:fredstov
    LVL 8

    Expert Comment

    The best way to do it is either by Log Shipping or Mirroring. They are the solutions intended to serve for this DR Scenario.

    The below article has the details to let you decide what to go with:

    More Links:

    LVL 28

    Accepted Solution

    Mirroring keeps the data in sync with constant replication, and then makes the failover transparent (assuming you're using the Native Client to connect to the database), but it also requires a third server to act as the witness, so that's a consideration. Also, the "mirrored" database isn't queryable - only the primary server is - so you can't select any data from the mirror database. Maybe not a concern, but it's always bothered me that the failover database isn't visible to confirm that everything is working properly.

    Replication/Log Shipping would also work, and you can set it on whatever interval you want (real-time, hourly, daily) to do the updates to the standby server. You'd need some way to tell clients to use the other server in the event of an outage, though - while we use a DNS alias (we have SERVER1 and SERVER2, and the dns alias SQLSERVER points to whichever one is active at the moment), this change can result in some downtime while clients switch over.

    As with all things, there are advantages/disadvantages to each way - the first link provided by dba2dba seems to give a good comparison overview. I've always favored log shipping since it gives you more control, but there are situations for both.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now