Replication Question -- Advice Really

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 2000 Forums

SQL Server Development (2000)

Replication Question -- Advice Really

Author

Topic

mj
Starting Member

25 Posts

Posted - 2003-01-16 : 09:02:41

I am setting up replication on a DB, and would like to know what the best mode would be. Here is the scenario:

DB has 99.999% uptime, however, I need to have a hot spare as a back up. Clustering is not an option (price).

Need to be able to switch to b/u db if main db fails, then have any records written to b/u db pushed back to main db.

Is merge replication the best scenario for this? Or would some one suggest a different method.

Thanks,

mj

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-16 : 09:14:22

how is clustering more expensive? you have to purchase the two machines which I assume you have, and you have to have Windows 2000 Server licenses ... then you just setup a cluster service on the machines and your ready to go unless there is something i'm missing... and it would handle this automatically ... if DB1 goes down it no longer owns the mdf and ldf files right? so it tells DB2 that it owns it and it picks back up from there ...

mj
Starting Member

25 Posts

Posted - 2003-01-16 : 09:18:03

I'm running standard edition, my bad, afaik, you can't cluster on standard, you need enterprise.

If this is incorrect, please let me know.

Thanks

mj

quote:

how is clustering more expensive? you have to purchase the two machines which I assume you have, and you have to have Windows 2000 Server licenses ... then you just setup a cluster service on the machines and your ready to go unless there is something i'm missing... and it would handle this automatically ... if DB1 goes down it no longer owns the mdf and ldf files right? so it tells DB2 that it owns it and it picks back up from there ...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-16 : 09:26:20

Why would an organization have a 5-nines requirement for a non-mission-critical database? (The implied question there is why would an organization use the Standard Edition for an important db?)

Replication is supported in the Standard Edition. You will not be able to maintain 99.999% with a replication based fail over solution. You will be closer with transactional than merge replication, but since neither will meet your requirement, who cares?

I would tell the decision makers to either cough up the dough to do it right (cluster or maybe log shipping) or accept that you can't do 5-nines.

Jay White
{0}

mj
Starting Member

25 Posts

Posted - 2003-01-16 : 09:38:08

Why would the replication not be able to maintain the uptime? Kind of confused on that?

Is another option to use DTS to keep the data current on server 2?

Server One currently has 5-nines history, tolerance for the system to be down is about an hour or so, maybe a little more.

I appreciate your help, and anymore you may be able to offer, thanks

mj

quote:

Why would an organization have a 5-nines requirement for a non-mission-critical database? (The implied question there is why would an organization use the Standard Edition for an important db?)

Replication is supported in the Standard Edition. You will not be able to maintain 99.999% with a replication based fail over solution. You will be closer with transactional than merge replication, but since neither will meet your requirement, who cares?

I would tell the decision makers to either cough up the dough to do it right (cluster or maybe log shipping) or accept that you can't do 5-nines.

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-16 : 09:54:12

Well, let's say you have transaction replication running and your DB1 server and DB2 server are in sync. DB1 is the backend to your website. Now some clumsy programmer trips over his hacky-sack and spills a frappacino into DB1. Now, you've probably got a list of things that need to happen so that DB2 can become the backend to your website. You can have things up an running again ... Maybe you've even got some fancy code in you connection logic that recognizes DB1 doesn't exist and it then uses DB2. It will be close, but maybe you can to 99.999% from you website ... but, your database is not 99.999% ( you did say the db had the uptime, right?). Any other consumers will no longer see their data.

Its a negligable distinction. But one that is very important to a dba's job. Do you think you can return service to all your consumers (rename DB2 to DB1) within an hour by using transactional replication? Is an hour your requirement or 99.999%?

I'd recommend taking some time and fully understanding disaster recovery and how the different options will effect your organization.

Jay White
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-16 : 12:00:35

MJ,
Log shipping might be more appropriate for you.

Jonathan
{0}

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-16 : 15:52:38