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

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

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:58:21 PM UTC-4, Mark D Powell wrote:
> 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

Update - I actually found where it states everything can be on one server in the BOL.  It is under the directions for using SSMS to set up replication.  

Mark 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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