Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1076

Re: Can I use Transaction Replication to Replicate to a database in the same instance

Show key headers only | View raw

On Thursday, May 24, 2012 5:34:33 PM UTC-4, Erland Sommarskog wrote:
> Mark D Powell (Mark.Powell2@hp.com) writes:
> > I was looking at using transaction replication to replicate all inserts
> > and updates to a database to an archive database.  The customer wants to
> > create the archive database on the same server where both the existing
> > database and the applicaiton runs. 
> > 
> > All the BOL documentation I have looked at so far only mentions using
> > another server as the target but does the replicated database server
> > actually have to be a different physical server? 
> 
> They can indeed be on the same database. I think I have something 
> that set up on my server here at home, as result of playing around
> with replication a couple months back.
> 
> The reason that BOL does not discuss this case is that most in real-world 
> replication scenarios, you want to replicate to a different server for
> various reasons. (Fallback, better response time at remote site etc.)
> 
> 
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

That is what I was thinking.  From looking at the stored procedure documentation  nothing indicates it would not work, but I did not just want to assume it would work without trying to get some confirmation since the documentation does not cover the "oh, by the way" type information.

Thanks,

-- Mark D Powell --




On Thursday, May 24, 2012 5:34:33 PM UTC-4, Erland Sommarskog wrote:
> Mark D Powell (Mark.Powell2@hp.com) writes:
> > I was looking at using transaction replication to replicate all inserts
> > and updates to a database to an archive database.  The customer wants to
> > create the archive database on the same server where both the existing
> > database and the applicaiton runs. 
> > 
> > All the BOL documentation I have looked at so far only mentions using
> > another server as the target but does the replicated database server
> > actually have to be a different physical server? 
> 
> They can indeed be on the same database. I think I have something 
> that set up on my server here at home, as result of playing around
> with replication a couple months back.
> 
> The reason that BOL does not discuss this case is that most in real-world 
> replication scenarios, you want to replicate to a different server for
> various reasons. (Fallback, better response time at remote site etc.)
> 
> 
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx



On Thursday, May 24, 2012 5:34:33 PM UTC-4, Erland Sommarskog wrote:
> Mark D Powell (Mark.Powell2@hp.com) writes:
> > I was looking at using transaction replication to replicate all inserts
> > and updates to a database to an archive database.  The customer wants to
> > create the archive database on the same server where both the existing
> > database and the applicaiton runs. 
> > 
> > All the BOL documentation I have looked at so far only mentions using
> > another server as the target but does the replicated database server
> > actually have to be a different physical server? 
> 
> They can indeed be on the same database. I think I have something 
> that set up on my server here at home, as result of playing around
> with replication a couple months back.
> 
> The reason that BOL does not discuss this case is that most in real-world 
> replication scenarios, you want to replicate to a different server for
> various reasons. (Fallback, better response time at remote site etc.)
> 
> 
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Can I use Transaction Replication to Replicate to a database in the same instance Mark D Powell <Mark.Powell2@hp.com> - 2012-05-24 10:44 -0700
  Re: Can I use Transaction Replication to Replicate to a database in the same instance Erland Sommarskog <esquel@sommarskog.se> - 2012-05-24 23:34 +0200
    Re: Can I use Transaction Replication to Replicate to a database in the same instance Mark D Powell <Mark.Powell2@hp.com> - 2012-05-24 14:58 -0700
      Re: Can I use Transaction Replication to Replicate to a database in the same instance Mark D Powell <Mark.Powell2@hp.com> - 2012-05-30 10:32 -0700

csiph-web