Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail From: Mark D Powell Newsgroups: comp.databases.ms-sqlserver Subject: Re: Can I use Transaction Replication to Replicate to a database in the same instance Date: Wed, 30 May 2012 10:32:57 -0700 (PDT) Organization: http://groups.google.com Lines: 117 Message-ID: <1620fc4e-5e28-4d41-8ba5-1916f557355f@googlegroups.com> References: <3dc516f2-7686-4bf1-9b12-2c9fbd318470@googlegroups.com> <06932057-ed0f-420e-9d29-b084ed3e2048@googlegroups.com> NNTP-Posting-Host: 15.219.153.73 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1338399177 16725 127.0.0.1 (30 May 2012 17:32:57 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 30 May 2012 17:32:57 +0000 (UTC) In-Reply-To: <06932057-ed0f-420e-9d29-b084ed3e2048@googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=15.219.153.73; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u User-Agent: G2/1.0 X-Received-Bytes: 5968 Xref: csiph.com comp.databases.ms-sqlserver:1091 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 inser= ts > > > and updates to a database to an archive database. The customer wants= to > > > create the archive database on the same server where both the existin= g > > > database and the applicaiton runs.=20 > > >=20 > > > 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?=20 > >=20 > > They can indeed be on the same database. I think I have something=20 > > that set up on my server here at home, as result of playing around > > with replication a couple months back. > >=20 > > The reason that BOL does not discuss this case is that most in real-wor= ld=20 > > replication scenarios, you want to replicate to a different server for > > various reasons. (Fallback, better response time at remote site etc.) > >=20 > >=20 > >=20 > >=20 > > --=20 > > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > >=20 > > 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 >=20 > That is what I was thinking. From looking at the stored procedure docume= ntation nothing indicates it would not work, but I did not just want to as= sume it would work without trying to get some confirmation since the docume= ntation does not cover the "oh, by the way" type information. >=20 > Thanks, >=20 > -- Mark D Powell -- >=20 >=20 >=20 >=20 > 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 inser= ts > > > and updates to a database to an archive database. The customer wants= to > > > create the archive database on the same server where both the existin= g > > > database and the applicaiton runs.=20 > > >=20 > > > 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?=20 > >=20 > > They can indeed be on the same database. I think I have something=20 > > that set up on my server here at home, as result of playing around > > with replication a couple months back. > >=20 > > The reason that BOL does not discuss this case is that most in real-wor= ld=20 > > replication scenarios, you want to replicate to a different server for > > various reasons. (Fallback, better response time at remote site etc.) > >=20 > >=20 > >=20 > >=20 > > --=20 > > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > >=20 > > 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 >=20 >=20 >=20 > 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 inser= ts > > > and updates to a database to an archive database. The customer wants= to > > > create the archive database on the same server where both the existin= g > > > database and the applicaiton runs.=20 > > >=20 > > > 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?=20 > >=20 > > They can indeed be on the same database. I think I have something=20 > > that set up on my server here at home, as result of playing around > > with replication a couple months back. > >=20 > > The reason that BOL does not discuss this case is that most in real-wor= ld=20 > > replication scenarios, you want to replicate to a different server for > > various reasons. (Fallback, better response time at remote site etc.) > >=20 > >=20 > > --=20 > > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > >=20 > > 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 i= n the BOL. It is under the directions for using SSMS to set up replication= . =20 Mark=20