patternsqlMinor
Clarification on Database Replication
Viewed 0 times
databaseclarificationreplication
Problem
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 ?
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 ?
Solution
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)
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.
It depends -
-
If you are replicating whole database and the size of database is huge
- then its best to initialize replication from database backup.
-
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.
Context
StackExchange Database Administrators Q#123588, answer score: 2
Revisions (0)
No revisions yet.