6 Steps to setting up Replication with Mirroring

/******************************************************************************
1. Setup Distribution on Distributor

!!!!!!!!RUN ONLY ON THE DISTRIBUTOR!!!!!!!!

Use this script on the distributor to setup distribution and add publishers.

Replace the value in @distributor to the name of the distributor
Replace the value in @password to the password you will use to allow publishers
Replace the value in @database to the distribution database name
@security_mode: 1 = Windows Authentication, 0 = SQL Server
Replace the value in @publisher with the name of the publisher you will setup
Replace the value in @distribution_db to the distribution database
*******************************************************************************/
--sets up distributor with remote setup password
use master
exec sp_adddistributor @distributor = N'distserv', @password = N'password123';
GO

--sets up the distribution database name to be used with Windows Auth
use master
exec sp_adddistributiondb @database = N'distribution', @security_mode = 1;
GO

--adds an authorized principal publisher that will use Windows Auth
use distribution
exec sp_adddistpublisher @publisher = N'pubserv', @distribution_db = N'distribution', @security_mode = 1;
GO

--adds an authorized mirror publisher that will use Windows Auth
use distribution
exec sp_adddistpublisher @publisher = N'mirrorserv', @distribution_db = N'distribution', @security_mode = 1;
GO



/******************************************************************************
2. Setup Remote Distributor on the Publisher

!!!!!!!!!!RUN ON PRINCIPAL AND MIRROR!!!!!!!!!!

Use this script on the publisher to add the remote distributor.
Replace the value in @distributor to the name of the distributor.
Replace the value in @password to the password setup on the remote distributor.
*******************************************************************************/
--sets up remote distributor on publisher
use master
exec sp_adddistributor @distributor = N'distserv', @password = N'password123'
GO



/*********************************************************************************************************
3. Create Publication on Publisher

!!!!!!!!!!SHOULD ONLY BE RUN ON THE PRINCIPAL!!!!!!!!!!!!!

This script will create a new publication and add all articles below. You only need to do this on the 
principal publisher because it will be transfered to the mirror publisher during a mirror failover.
Replace the value in @dbname with the database you will be publishing
Replace the value in @publication with a name for your publication
Replace the value in @description with any description you would like to give
The value in @alt_snapshot_folder should be set to the value of the snapshot folder setup on the distributor
Replace the value in @article with a new article name of your choosing
Replace the value in @source_object with the logical name of the article (table, view, etc.)
Replace the value in @destination_table with the table name on the subscriber
Replace the value in @subscriber with the name of the subscriber server
Replace the value in @destination_db with the name of the database on the subscriber
Be sure to replace instances of 'mydatabase' with the appropriate values of your database
**********************************************************************************************************/
--Enables publication on database
exec sp_replicationdboption @dbname = N'mydatabase', @optname = N'publish', @value = N'true'
GO
USE [mydatabase]
--Creates a log reader agent job
exec sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
--Creates a queue reader agent job
exec sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
--Adds a publication
exec sp_addpublication @publication = N'mypub', @description = N'Transactional publication with updatable subscriptions of database ''mydatabase'' from Publisher ''pubserv''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = N'\\server\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
--Adds a publication snapshot
exec sp_addpublication_snapshot @publication = N'mypub', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
--Grants access to specific logins
exec sp_grant_publication_access @publication = N'mypub', @login = N'sa'
GO
--Adds all articles to be replicated
exec sp_addarticle @publication = N'mypub', @article = N'MyTableName', @source_owner = N'dbo', @source_object = N'MyTableName', @type = N'logbased', @description = N'', @creation_script = null, @pre_creation_cmd = N'drop', @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_table = N'MyTableName', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false'
GO
--Continue adding articles until you have all articles listed that you want to publish


--Adds a pull subscription at the publisher
exec sp_addsubscription @publication = N'mypub', @subscriber = N'subserver', @destination_db = N'destmydatabase', @subscription_type = N'Pull', @sync_type = N'automatic', @article = N'all', @update_mode = N'queued failover', @subscriber_type = 0
GO


/*****************************************************************
4. Add Failover Parameters to Agents on Distributor
 (for trans replication w/ updatable subs & mirroring)

!!!!!!!!!RUN ONLY ON THE DISTRIBUTOR!!!!!!!!!!

Run the following to add the failover partner name parameter to
the Snapshot Agent, Log Reader Agent and Queue Reader Agent.
Replace the value in @parameter_value with the name of your mirror
******************************************************************/
USE [master]

--exec sp_help_agent_profile
exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'mirrorpubserv'
exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'mirrorpubserv'
exec sp_add_agent_parameter @profile_id = 9, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'mirrorpubserv'



/***********************************************************************************************************
5. This script will create a new subscription on the publisher.

!!!!!!!!!RUN ONLY ON THE SUBSCRIBER!!!!!!!!!

The value in @alt_snapshot_folder should be set to the value of the snapshot folder setup on the distributor
Be sure to replace mysubscriberdb with the appropriate value for your subscriber database
Replace the value in @publisher with the name of the principal publisher
Replace the value in @publication with the name of the publication you setup in step 3
Replace the value in @publisher_db with the name of the database from step 3 @dbname
Replace the value in @distributor with the name of the distributor from step 1
Replace the value in @login with the SQL Server login to use to access the distributor from the subscriber
Replace the value in @password with the SQL Server login password for the login above
The value in @alt_snapshot_folder should be set to the value of the snapshot folder setup on the distributor
************************************************************************************************************/
use [mysubscriberdb]
exec sp_addpullsubscription @publisher = N'pubserv', @publication = N'mypub', @publisher_db = N'mydatabase', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'queued failover', @immediate_sync = 1
exec sp_link_publication @publisher = N'pubserv', @publication = N'mypub', @publisher_db = N'mydatabase', @security_mode = 0, @login = N'mylogin', @password = N'mypassword', @distributor = N'distserv'
exec sp_addpullsubscription_agent @publisher = N'pubserv', @publication = N'mypub', @publisher_db = N'mydatabase', @distributor = N'distserv', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20100512, @active_end_date = 99991231, @alt_snapshot_folder = N'\\server\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO


/*********************************************************************************************************
6. Start Snapshot Agent

This script will start the snapshot agent on the distributor to begin synchronization when you are ready.

!!!!!!!!!RUN ONLY ON THE PUBLISHER!!!!!!!!!

Be sure to replace instances of 'mydatabase' with the appropriate values of your database
**********************************************************************************************************/
USE [mydatabase]
EXEC sp_startpublication_snapshot @publication = 'mypub'