Replication as backup - Microsoft SQL Server

Ian Wyld

Hello All

We intend to replicate a database in order to have it as a near immediate
standby in case of the failure of the main server.

Is this the best solution for disaster recovery?

We are currently testing our replication plan. When we set up the
subscriber, a snapshot of the publisher is created and this takes hours. Is
there a better way?

My understanding is that a RAID array would help in the case of the failure
of a single disc in the server.

What about log shipping. How does log shipping work with identity columns?

Does anyone have any experience of this? Can you offer advice and guidance?

Regards

Ian

Jul 20 '05 #1

Subscribe Reply

1605

Simon Hayes

"Ian Wyld" <ia*****@tiscal i.co.uk> wrote in message
news:41******** @mk-nntp-2.news.uk.tisca li.com...

Hello All

We intend to replicate a database in order to have it as a near immediate
standby in case of the failure of the main server.

Is this the best solution for disaster recovery?

We are currently testing our replication plan. When we set up the
subscriber, a snapshot of the publisher is created and this takes hours.
Is
there a better way?

My understanding is that a RAID array would help in the case of the
failure
of a single disc in the server.

What about log shipping. How does log shipping work with identity columns?

Does anyone have any experience of this? Can you offer advice and
guidance?

Regards

Ian

I don't really know from your description what you mean by "disaster
recovery" - this page covers some of the high-availability options you have
with MSSQL:

http://www.microsoft.com/sql/techinf...ailability.asp

One issue with a replicated database as a standby is that if the primary
fails, then your applications need to be reconfigured with the new server
and database name; if you need failover which is transparent to your
clients, then clustering is the usual solution.

For information on optimizing the replication initial snapshot, see here:

http://www.microsoft.com/technet/pro.../tranrepl.mspx

RAID will protect you against losing one or more disks, depending on the
configuration, as will a NAS or SAN, although MSSQL is only supported on
NAS/SAN solutions certified for it:

http://support.microsoft.com/default...&Product=sql2k

Log shipping copies every transaction from one database to another by
copying transaction log backups and then restoring them. The secondary
database is always offline so the logs can be restored as they arrive from
the primary server - that means no changes can be made (it can't even be
read), so identity values aren't an issue. Log shipping also has the same
application reconfiguration issue as replication, of course.

Log shipping is a simple solution, but the secondary database is offline and
you would lose (at best) minutes of data if the primary goes down.
Replication is more complex, but the secondary database can be online, and
you can limit data loss to seconds rather than minutes. Clustering is
probably the most expensive solution, but you can lose a whole server and
still carry on with no interruption.

Simon

Jul 20 '05 #2

John Smith

Consider log shipping instead of replication if all you need it DR.

"Ian Wyld" <ia*****@tiscal i.co.uk> wrote in message
news:41******** @mk-nntp-2.news.uk.tisca li.com...

Hello All

We intend to replicate a database in order to have it as a near immediate
standby in case of the failure of the main server.

Is this the best solution for disaster recovery?

We are currently testing our replication plan. When we set up the
subscriber, a snapshot of the publisher is created and this takes hours.
Is
there a better way?

My understanding is that a RAID array would help in the case of the
failure
of a single disc in the server.

What about log shipping. How does log shipping work with identity columns?

Does anyone have any experience of this? Can you offer advice and
guidance?

Regards

Ian

Jul 20 '05 #3

Similar topics

1950

Replication on shared mySQL server

by: Stein Rustad | last post by:

Is it possible to set up replication on a shared mySQL server, i.e. have the replication only concern the databases that I have access to, without changing the entire server setup? (This will probably be in a shell account on a Linux box) I basically have 2 server, one which I have full controll over and a second (backup) that I will share with other people. I want to set up a two-way replication between these, so that server 1 can go...

MySQL Database

1940

replication and log shipping

by: Eric | last post by:

Hi, Ive a few questions and would be grateful for any assistance/comments from experience as Ive not that much dba experience , we have 3 servers all containing sql 2000 standard edition, two (prod and standby) located in the U.S. and Im hoping to set up the third as a reporting server locally in Europe...presumably having the data close as possible and as current as possible for reporting?...We dont have the resources to implement...

Microsoft SQL Server

2266

DB2 and File Replication

by: Tony Hinkle | last post by:

I am working on an application note to provide instructions on how to use a real-time file replication application to provide disaster recovery for DB2. I basically need to know how to identify what files need to be replicated for the default databases and others that are mounted. I would greatly appreciate any offered assistance. Thanks!

DB2 Database

4370

db2 replication vs log shipping

by: GB | last post by:

Hi, I'm trying to find an easy, efficient and safe site-to-site backup/failover solution. The idea is to have anytime a remote copy of 99.99% of the data. Productive db -> VPN (internet) -> Backup site source destination

DB2 Database

2084

DB 7.2 Replication And DR

by: johnm | last post by:

Here is my situation. We run a very mission critical application that uses DB2 7.2 for the data repository. The database is hosed at a data center in Los Angeles, Ca. Our main office is in Portland, Oregon.

DB2 Database

2357

Prevent Infinite Looping in Q Replication

by: RdR | last post by:

Hi, Has anyone encountered infinite looping in Q Replication? This happens when I have a source DB2 table A going to a target DB2 table B, it also happens that the samne target table B is replicated back to source table A (true bi-directional replication scenario). Once I start replication on a master to master scenario the changes in A gets replicated to B but that change gets replicated back to A and so on creating an infinite loop. Is...

DB2 Database

1709

Replication: very strange problem

by: Ondine | last post by:

Hi I have a client running an Access 2000 database on a small network with 3 pc's. Two of the laptop pcs have a data replica, which they use when not connected to the network, the 'server' being the 3rd machine. The other day they reported that 'suddenly' a whole load of data that they had entered (on to the 'server' main data) that day had 'disappeared'. Not only had the new data gone, but updates performed

Microsoft Access / VBA

6007

Access Back-End Over the Internet?

by: Raphi | last post by:

Hi, I've been using an Access application I wrote for an office with the front-end stored on all computers and the back-end on one of them serving as an Access file server. Now we're moving to a 2nd office 15 minutes down the road. Only one office will be open at a time, so theoretically it'd be possible to copy the back-end manually every night from one office to another, but frankly, that's pretty annoying.

Microsoft Access / VBA

1316

Security - FE BE - Replication

by: gjameson via AccessMonster.com | last post by:

I am nearing the end of my development for my application. Here is my scenario. A few users will use this on the lan. ---- FE BE ----Security A few users will use this out in the field and come in and sync the db. (am & pm) ----- Replication------Security I know I need to split FE BE and do replication and need security, also do a backup somewhere.

Microsoft Access / VBA

7245

Replication - binary log auto removal after processing

by: tech101 | last post by:

Can I get the master (or slaves) to automatically remove the binary logs once they are processed by all slaves? It says in the mysql manual : If you are using replication, you should not delete old binary log files until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are...

MySQL Database

10039

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9881

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10923

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10542

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9732

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

8100

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

7256

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4778

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3368

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General