Replication as backup

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"Ian Wyld" <ianwyld@.tiscali.co.uk> wrote in message
news:41507254_1@.mk-nntp-2.news.uk.tiscali.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/techin...vailability.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/pr...n/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/defaul...1&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|||Consider log shipping instead of replication if all you need it DR.

"Ian Wyld" <ianwyld@.tiscali.co.uk> wrote in message
news:41507254_1@.mk-nntp-2.news.uk.tiscali.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

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

http://msdn2.microsoft.com/en-us/library/ms182792.aspx

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...

Replication and distribution: Agents

Hi all replication experts...

I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.

There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?

Grateful for answers

Best,
/M

Its hard to say exactly what this is, but it appears to be your distribution agent. You appear to have a pull agent configured on your subscriber.

It is essential for your transactional replication topology. Do not remove it.

Hilary

|||OK, thanks :)

Replication and backups

Hi All,
Recently one of our servers went down and I need to restore a database
from backup. This database was the main publisher and distributor for a
two server replication scheme. My question is if I restore the main
publisher's table from a backup need I do anything to the subscriber?
Will I have to restore the subscriber from the backup as well? Thanks
for any help with this!
Regards,
Troy
the distribution database contains the commands which should be applied on
the subscriber to keep it synchronized with the publisher. There is a
tracking column on the distributor which is compared with the same tracking
column on the subscriber. The distribution agent compares the value of this
column to determine what commands to apply on the subscriber.
If you restore an earlier version of the distribution database to the
subscriber and the commands are still in the distribution database
everything will be fine. If they have been removed you will have to
re-initialize your subscriber.
"Troy" <poster[no-spam]@.designguide.com> wrote in message
news:MPG.1b0469879be23858989687@.msnews.microsoft.c om...
> Hi All,
> Recently one of our servers went down and I need to restore a database
> from backup. This database was the main publisher and distributor for a
> two server replication scheme. My question is if I restore the main
> publisher's table from a backup need I do anything to the subscriber?
> Will I have to restore the subscriber from the backup as well? Thanks
> for any help with this!
> --
> Regards,
> Troy

Replicating Sql Server to Express Edition as backup application

Hello!

We are running a business critical client-server application that needs to have high availability to the end user.
I was wondering if it would be possible to setup a replication/data transfer from an existing Sql Server 7.0/8.0/9.0 (regular online environment running on server) TO an instance of Sql 2005 Express Edition (backup env. running on klient PC).
In case of server failure the application would redirect - accessing the local PC Express edition.

Does anyone have any input on this, and if it would be a possible alternative to use as extra standby- system. ?

Kind regards
TomasYes this is possible but there are certain exceptions.
And it depends on what kind of replication technology you are looking at.
SQL Server 2005 Books Online has more information on supported topologies.

Search for "Using Multiple Versions of SQL Server in a Replication Topology"|||

Hi Tomas.

It would appear that I have a similar type of need. See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=677662&SiteID=1 which describes my situation. If you've got any further info on how this is achieved please respond. I'll do the same. We were able to create the windows form app and use Sql2005 Std to maintain a 'gold' standard db on our publisher/distributor machine. We have to replace our db daily with an entirely new dataset that is sent to us via a foreign host. We take that data an bulk copy it into our db. Then, we setup a snapshot replication scenario, installing SqlExpress w/ Advance Tools on the subscriber machine.

We install our offline viewer windows smart client application on the subscriber, and are able to bring in a view of the needed data. However, we need this view to be available offline, for use when the prefferred application is unavailable or when the network is down. At this point, if the network is down our replicated db is not available either. So, I'm lost with the way I've got it working now. I'm not sure if it is a setup issue or if I've misunderstood how replication works. At any rate, our need appears to be similar in scope to yours. I'll check back here, and on my post to update the status.

Right now, we are examining our setup of the replication subscription and whether there is a way to achieve the goal desired, or if we have to employ a bulk copy insert into a local database for our subscriber downtime usage.

|||

Hi,

You might want to talk to us about a product that we sell (WANSync) which provides real-time asynchronous data replication, full tree replication topologies to protect from server and site failures. Should an SQL database server fail for any reason, a geographically remote SQL replica takes it's place and will allow you to keep working.

If you want more information or want to trial this solution for free, please email me at paul.maziere@.dataplex-systems.com

Regards, Paul.

Replicating Sql Server to Express Edition as backup application

Hello!

We are running a business critical client-server application that needs to have high availability to the end user.
I was wondering if it would be possible to setup a replication/data transfer from an existing Sql Server 7.0/8.0/9.0 (regular online environment running on server) TO an instance of Sql 2005 Express Edition (backup env. running on klient PC).
In case of server failure the application would redirect - accessing the local PC Express edition.

Does anyone have any input on this, and if it would be a possible alternative to use as extra standby- system. ?

Kind regards
TomasYes this is possible but there are certain exceptions.
And it depends on what kind of replication technology you are looking at.
SQL Server 2005 Books Online has more information on supported topologies.

Search for "Using Multiple Versions of SQL Server in a Replication Topology"|||

Hi Tomas.

It would appear that I have a similar type of need. See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=677662&SiteID=1 which describes my situation. If you've got any further info on how this is achieved please respond. I'll do the same. We were able to create the windows form app and use Sql2005 Std to maintain a 'gold' standard db on our publisher/distributor machine. We have to replace our db daily with an entirely new dataset that is sent to us via a foreign host. We take that data an bulk copy it into our db. Then, we setup a snapshot replication scenario, installing SqlExpress w/ Advance Tools on the subscriber machine.

We install our offline viewer windows smart client application on the subscriber, and are able to bring in a view of the needed data. However, we need this view to be available offline, for use when the prefferred application is unavailable or when the network is down. At this point, if the network is down our replicated db is not available either. So, I'm lost with the way I've got it working now. I'm not sure if it is a setup issue or if I've misunderstood how replication works. At any rate, our need appears to be similar in scope to yours. I'll check back here, and on my post to update the status.

Right now, we are examining our setup of the replication subscription and whether there is a way to achieve the goal desired, or if we have to employ a bulk copy insert into a local database for our subscriber downtime usage.

|||

Hi,

You might want to talk to us about a product that we sell (WANSync) which provides real-time asynchronous data replication, full tree replication topologies to protect from server and site failures. Should an SQL database server fail for any reason, a geographically remote SQL replica takes it's place and will allow you to keep working.

If you want more information or want to trial this solution for free, please email me at paul.maziere@.dataplex-systems.com

Regards, Paul.

Replicating Multiple Databases

I am currently using snapshot replication to backup a single SQL database to
a server at home. What technique(s) would be best if I wanted to backup say
6 databases? These would all be one-way as no changes are made to the data
from the backup server. Also, the data is not SO critical so as not to be
able to afford the loss of up to 24 hours worth of changes from one to the
other. I've considered doing 6 Publications-Subscriptions etc, but I
thought there must be a better way. Is there?
Thanks
Carlos, the publications are on a per-database basis, so there isn't a
simpler method than the one you're considering. Potential alternatives (not
simpler) include log shipping and transactional replication. If you're
concerned about bandwidth issues then these would be more suitable.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If you have big pipes between your publisher and subscribers I would
recommend log shipping. Otherwise I would recommend transactional
replication as only the changes will be replicated.
For transactional replication you will need a pk on each table; although
there are ways around this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Carlos Y. Maldonado, Ph.D." <cmaldonado@.earthlink.net> wrote in message
news:U5i4f.14372$vw6.9184@.newsread1.news.atl.earth link.net...
>I am currently using snapshot replication to backup a single SQL database
>to a server at home. What technique(s) would be best if I wanted to backup
>say 6 databases? These would all be one-way as no changes are made to the
>data from the backup server. Also, the data is not SO critical so as not
>to be able to afford the loss of up to 24 hours worth of changes from one
>to the other. I've considered doing 6 Publications-Subscriptions etc, but
>I thought there must be a better way. Is there?
> Thanks
>