Sql-server – Clarification on Database Replication

replicationsql serversql-server-2008

SQL Server 2008

I have to do database replication which is a new for me.

Goal is have mirror database on servers 2 and 3.

When we set up database replication in the Server 2, 3 from Server # 1, do I need to take DB backup for Server # 2 and Server # 3 as well?

How do I test whether my databases are running fine even on the failure of Server # 1 ?

Best Answer

When we set up Database Replication in the Server 2, 3 from Server # 1, Do i need to take DB backup for Server # 2 and Server # 3 as well.

It depends -

  • If you are replicating whole database and the size of database is huge

  • IF you are replicating subset of data (as opposed to the entire database)

    • then you just run the snapshot (behind the scene it does a bcp out and bcp in of the data).

How do i test whether my databases are running fine even on the failure of Server # 1 ?

This will eventually depend on the frequency of log reader agent. If you set it to run continuously then you will see your changes immediately on the subscribers. The latency depends on the amount of activity at publisher (server 1), network latency, distribution database - if it is on the same server as publisher or is on its own dedicated server.

There are many factors to consider when you choose replication as Disaster recovery (a stand by server when your primary is down).

As a side note:

What you are looking is a good HA - case when server 1 is down .. do we have a standby that can serve the application ? .. something along that line.

You should consider moving to a more recent version - sql server 2014 and look into using AlwaysON Availability groups.

Related Question