Request for advices to set up replication

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Request for advices to set up replication

Author

Topic

Harkonnen
Starting Member

3 Posts

Posted - 2010-06-15 : 09:14:14

Hi everybody,

We currently have 2 servers: A and B.

Server A contains a production version of a SQL Server 2008 database and B contains a development version of that database.

We wish to establish a replication of these databases as
follows:
- The production database will be replicated from A to B,
- The development database will be replicated from B to A

The goal is to always have a server online for production
and development in case of failure of one or the other.

I intended to establish a transactionnal replication system, but have never done this manipulation, I'm not sure this is the best way.

Could you tell me the advantages and disadvantages of this
method, and a better way to proceed if there is one ?

Thank you in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 13:14:56

There is a huge disadvantage to this! If development accidentally deletes all of the data in a table on serverB, then that will get replicated to production! I would never recommend replicating from dev to prod due to this.

Can you instead just use database mirroring?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Harkonnen
Starting Member

3 Posts

Posted - 2010-06-17 : 07:22:34

Hi, and thanks for your answer

I cant't see where the problem is : if development deletes a table on dev database on server A replicated to dev databas on server B, why would the production database be affected ?
Could you explain me what I advvantage mirroring has over replication ?

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-17 : 14:34:19

Let's say your most important table is called Table1 and it is being replicated from dev to prod. And the developer accidentally deletes all data from this table in the dev environment. That delete statement will get replicated to prod and wipe out the data there, thus affecting your users.

The advantage of mirroring is that it is high availability and DR solution, replication is not. Mirroring doesn't allow any data access to the mirrored database, so no one can break production.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources