We've got a new server we want to migrate to and I have setup a Publication on the current production server as a Transactional publication. Since the subscribed database is up to date, I want to discontinue the subscription, rename the database, then point our database connections to it. The new server is SQL Server 2012 where the old server is 2008.
Thanks for any pointers.
Yes .. you can remove replication.
USING GUI :
On the publication server, Right Click on the subscription and delete it. Make sure you select "connect to subscriber" so that all the replication bits are properly cleared.
USING TSQL :
--- removes replication objects from the subscription database.
-- Remove replication objects from the subscription database .
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2008R2Replica'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO
---- script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution database.
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks2008R2';
-- Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;
-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;
-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO
Refer to How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)
MSDN Blog has How to cleanup Replication Bits
EDIT: Based on the comments :
For migration to new server, you are better off
If the database is not big (more than 500GB+) and you have a good maintenance window
Go for backup and restore method.
If the maintenance window is small then
Either you can choose between LogShipping or Database Mirroring.
When you want to cut over to the new server -
Above methods will help you to migrate your database to new server with minimum downtime. Obviously, there are many other pre and post migration steps that you need to be aware.
