Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Can I use Transaction Replication to Replicate to a database in the same instance Date: Thu, 24 May 2012 23:34:33 +0200 Organization: Erland Sommarskog Lines: 28 Message-ID: References: <3dc516f2-7686-4bf1-9b12-2c9fbd318470@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="5589"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18Y9QyCf9EJnt2xcXIyNp8j" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:ueK2w9K81s6VlkkYVtCQbEWCSxA= Xref: csiph.com comp.databases.ms-sqlserver:1075 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