/*************************************************************************************
sProc Name:	   spCreatePublications
Author:			Frank Bazan
Description:	This script:- 
				a)	Enables database for replication
				b)	Sets accounts for snapshot and logreader agent.
				c)	Adds a transaction publication
				d)	Adds articles
				Run on publisher server for any new publication.

Instructions
				1)		Ensure connected to publisher
				2)		Ensure that ServerControl.dbo.Publication and 
						ServerControl.dbo.Article table is populated as required.
				2)		Execute sProc.

History
***************************************************************************************
Vers	Author				Date			Description of Change
----	------				----			---------------------
1.0   Frank Bazan       22 May 07   Initial Version
***************************************************************************************/
IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
            WHERE ROUTINE_NAME = 'spCreatePublications'
            AND ROUTINE_TYPE = 'PROCEDURE' )
BEGIN
   DROP PROCEDURE spCreatePublications
END
GO

CREATE PROCEDURE [dbo].[spCreatePublications]

AS

SET NOCOUNT ON
-- Table variable holds the publication data.
DECLARE @Publications TABLE (	PublicationsId INT IDENTITY NOT NULL, --Used for outer loop
								PublicationId INT NOT NULL,
								DataUserDesc NVARCHAR(256),
								DataUserCode NVARCHAR(3) NOT NULL,
								PublicationDb NVARCHAR(128) NOT NULL,
								PublicationName VARCHAR(50) NOT NULL,
								PublisherSecurityMode INT NOT NULL,
								PublisherLogin NVARCHAR(128) NULL,
								PublisherPwd NVARCHAR(128) NULL,
								DistributorAgentLogin NVARCHAR(128) NULL,
								DistributorAgentPwd NVARCHAR(128) NULL						
)

-- Populate with ALL publications (There is a test in the SQL which check if a publication exists.
-- if it does it moves on to the next one).
INSERT INTO @Publications (
		PublicationId,
		DataUserDesc,
		DataUserCode,
		PublicationDb,
		PublicationName,
		PublisherSecurityMode,
		PublisherLogin,
		PublisherPwd,
		DistributorAgentLogin,
		DistributorAgentPwd )
SELECT	PublicationId,
		DataUserDesc,
		DataUserCode,
		PublicationDatabase,
		PublicationName,
		PublisherSecurityMode,
		PublisherLogin,
		PublisherPwd,
		DistributorAgentLogin,
		DistributorAgentPwd

FROM	ServerControl.dbo.Publication

DECLARE @PubCounter INT		SET @PubCounter = 1
DECLARE @MaxPubCount INT	SET @MaxPubCount = (SELECT MAX(PublicationsId) FROM @Publications)

-- Set up the loop
WHILE @PubCounter <= @MaxPubCount

	BEGIN
			DECLARE @mydb NVARCHAR(128)
			DECLARE @PublicationId INT
			DECLARE @PublishingDb NVARCHAR(128)
			DECLARE @PublicationName NVARCHAR(128)
			DECLARE @PublisherSecurityMode INT
			DECLARE @PublisherLogin NVARCHAR(128)
			DECLARE @PublisherPwd NVARCHAR(128)
			DECLARE @DistributorAgentLogin NVARCHAR(128)
			DECLARE @DistributorAgentPwd NVARCHAR(128)
			DECLARE @ConfigMsg NVARCHAR(500)

			SELECT	@mydb = 'master',
					@PublicationId = (SELECT PublicationId FROM @Publications WHERE PublicationsId = @PubCounter),
					@PublishingDb = (SELECT PublicationDb FROM @Publications WHERE PublicationsId = @PubCounter),
					@PublicationName = (SELECT '(' + DataUserCode + ')' + SPACE(1) + PublicationName FROM @Publications WHERE PublicationsId = @PubCounter),
					@PublisherSecurityMode = (SELECT PublisherSecurityMode FROM @Publications WHERE PublicationsId = @PubCounter),
					@PublisherLogin = (SELECT PublisherLogin FROM @Publications WHERE PublicationsId = @PubCounter),
					@PublisherPwd = (SELECT PublisherPwd FROM @Publications WHERE PublicationsId = @PubCounter),
					@DistributorAgentLogin = (SELECT DistributorAgentLogin FROM @Publications WHERE PublicationsId = @PubCounter),
					@DistributorAgentPwd = (SELECT DistributorAgentPwd FROM @Publications WHERE PublicationsId = @PubCounter)

			EXEC ('USE ' + @mydb + '


			SET NOCOUNT ON

			DECLARE @Cmd                    varchar(8000),
					@PublicationDatabase    varchar(30),
					@PublicationName        varchar(255),
					@PublicationDescription varchar(255),
					@ObjectList             cursor,
					@ObjectName             varchar(255),
					@SourceSchema			nvarchar(128), 
					@DestinationSchema		nvarchar(128),
					@Counter                int,
					@MaxCounter             int,
					@Status					int,
					@PublisherSecurityMode	int,
					@PublisherLogin			varchar(128),
					@PublisherPwd			varchar(128),
					@AgentLogin				varchar(128),
					@AgentPwd				varchar(128)
					

			DECLARE @Msg varchar(512),
					@NL  char(1),
					@SP  char(1),
					@SQ  char(1),
					@TAB char(1)

			SET @NL  = CHAR(10)
			SET @SP  = SPACE(1)
			SET @SQ  = CHAR(39)
			SET @TAB = CHAR(9)

			--*****************************************************************************
			-- Manage security (ensure variable is set to NULL if no credentials provided)
			--*****************************************************************************

			SET @PublisherLogin = NULLIF(''' + @PublisherLogin + ''','''')
			SET @PublisherPwd = NULLIF(''' + @PublisherPwd + ''','''')
			SET @AgentLogin = NULLIF(''' + @DistributorAgentLogin + ''','''')
			SET @AgentPwd = NULLIF(''' + @DistributorAgentPwd + ''','''')

			-- ************************************************
			-- Environment specific parameters are set below
			-- ************************************************

			SELECT @PublicationDatabase     = N''' + @PublishingDb + ''',
				   @PublicationName         = ''' + @PublicationName + ''',
				   @PublicationDescription  = ''Transactional publication of '' + @PublicationName + '' from '' + @PublicationDatabase + '' database on publisher '' + @@ServerName
			          
			-- ************************************************
			-- Enabling the database for replication
			-- ************************************************

			-- Is DB already enabled?
			DECLARE @EnabledDB TABLE (EnabledDbName NVARCHAR(128))
			INSERT INTO @EnabledDB
			EXEC sp_helpreplicationdb

			IF NOT EXISTS (SELECT EnabledDBName from @EnabledDB WHERE EnabledDbName = @PublicationDatabase )
			-- DB isn''t enabled for publication, so enable it.
			BEGIN
				SET @Msg = @NL + REPLICATE( ''-'' + @SP, 25 ) + @NL
						   + @NL + CONVERT( varchar, GETDATE(), 114 ) + @TAB + ''Enabling the database for replication...''
						   + @NL
				RAISERROR( @Msg, 0, 1 ) WITH NOWAIT
				print @PublicationDatabase
				EXEC sp_replicationdboption    
							@dbname             = @PublicationDatabase,
							@optname            = N''publish'',
							@value              = N''true'',
							@ignore_distributor = 0
			END

			USE ' + @PublishingDb + '


			/*****************************
			** Add the log reader agent **
			*****************************/

			-- Does logreader already exist?

			DECLARE @LogReaderInfo TABLE (id INT, 
									AgentName NVARCHAR(100),
									PublisherSecurityMode INT,
									PublisherLogin NVARCHAR(128),
									PublisherPassword NVARCHAR(524),
									JobId UNIQUEIDENTIFIER,
									JobLogin NVARCHAR(512),
									JobPassword NVARCHAR(128)
									)

			INSERT INTO @LogReaderInfo
			EXEC sp_helplogreader_agent

			IF NOT EXISTS ( SELECT 1 FROM @LogReaderInfo)
			BEGIN
			EXEC sp_addlogreader_agent 
					@job_login = @AgentLogin, 
					@job_password = @AgentPwd,
					@publisher_security_mode = ' + @PublisherSecurityMode + ',
					@publisher_login = @PublisherLogin,
					@publisher_password = @PublisherPwd
			END
	
			-- ************************************************
			-- Adding the transaction publication
			-- ************************************************


			EXEC sp_helppublication @Publication = @PublicationName, @found = @Status OUTPUT
			IF @Status = 0
			BEGIN
			SET @Msg = @NL + REPLICATE( ''-'' + @SP, 25 ) + @NL
					   + @NL + CONVERT( varchar, GETDATE(), 114 ) + @TAB + ''Adding the transactional publication "'' + @PublicationName + ''" ...''
					   + @NL
			RAISERROR( @Msg, 0, 1 ) WITH NOWAIT
			EXEC sp_addpublication       
						@publication                  = @PublicationName, 
						@sync_method                  = N''concurrent'', --''concurrent, native''
						@repl_freq                    = N''continuous'', --''continuous, snapshot''
						@description                  = @PublicationDescription, 
						@status                       = N''active'', 
						@allow_push                   = N''true'', 
						@allow_pull                   = N''true'', 
						@allow_anonymous              = N''false'', 
						@enabled_for_internet         = N''false'', 
						@independent_agent            = N''true'',	-- An exclusive distributor agent or shared? 
						@immediate_sync               = N''false'',	-- Only when a new subscription is created?
						@allow_sync_tran              = N''false'',   
						@autogen_sync_procs           = N''false'', 
						@retention                    = 0,			-- 336 Hours til subscription expires
						@allow_queued_tran            = N''false'',	-- This is a non updateable publication
						@snapshot_in_defaultfolder    = N''true'',	
						@compress_snapshot            = N''false'', 
						@ftp_port                     = 21,			-- @enabled_for_internet is ''false'' so irrelevant
						@ftp_login                    = N''anonymous'', -- see above
						@allow_dts                    = N''false'', 
						@allow_subscription_copy      = N''false'', 
						@add_to_active_directory      = N''false'',
						@replicate_ddl                = 0,			--schema changes NOT replicated
						@allow_initialize_from_backup = N''false'', 
						@enabled_for_p2p              = N''false'', 
						@enabled_for_het_sub          = N''false''

				/******************
				Add the articles **
				*******************/

				SET @Msg = @NL + REPLICATE( ''-'' + @SP, 25 ) + @NL
						   + @NL + CONVERT( varchar, GETDATE(), 114 ) + @TAB + ''Adding the articles...''
						   + @NL
				RAISERROR( @Msg, 0, 1 ) WITH NOWAIT

				DECLARE @RepList TABLE (	Table_NAME SYSNAME,
											SourceSchema NVARCHAR(128),
											DestinationSchema NVARCHAR(128) )

				--insert tables to be replicated
				INSERT @RepList ( Table_NAME, SourceSchema, DestinationSchema )
				SELECT ArticleName , SourceSchema, DestinationSchema
				FROM ServerControl.dbo.Article WHERE PublicationId = ''' + @PublicationId + '''
		
		
					-- This cursor grabs all tables from the table variable, and adds them as an article.
					SET @ObjectList = CURSOR FOR 
									  SELECT Table_Name AS [LocationData], SourceSchema, DestinationSchema
									  FROM @RepList             
									  ORDER BY Table_Name
					OPEN @ObjectList
					FETCH NEXT
					FROM @ObjectList
					INTO @ObjectName, @SourceSchema, @DestinationSchema

					WHILE @@Fetch_Status = 0
					   BEGIN
						  SET @Msg = ''Adding article '' + @ObjectName
						  RAISERROR( @Msg, 0, 1 ) WITH NOWAIT

						  EXEC sp_addarticle   
								   @publication                   = @PublicationName, 
								   @article                       = @ObjectName, 
								   @source_owner                  = @SourceSchema, 
								   @source_object                 = @ObjectName,
								   @destination_table             = @ObjectName,
								   @type                          = N''logbased'', 
								   @creation_script               = NULL, 
								   @pre_creation_cmd              = N''truncate'',
								   @schema_option                 = NULL,--0x000000000803509F,
								   @identityrangemanagementoption = N''manual'', 
								   @destination_owner             = @DestinationSchema, 
								   @vertical_partition            = N''false'',
								   @ins_cmd                       = N''SQL'',
								   @del_cmd                       = N''SQL'',
								   @upd_cmd                       = N''SQL''

						  FETCH NEXT
						  FROM @ObjectList
						  INTO @ObjectName, @SourceSchema, @DestinationSchema
					   END

					-- Close Cursor
					CLOSE @ObjectList
					DEALLOCATE @ObjectList

			EXEC sp_addpublication_snapshot		@publication = @PublicationName, 
												@frequency_type = 1, 
												@frequency_interval = 1, 
												@frequency_relative_interval = 1, 
												@frequency_recurrence_factor = 0, 
												@frequency_subday = 8, 
												@frequency_subday_interval = 1, 
												@active_start_time_of_day = 0, 
												@active_end_time_of_day = 235959, 
												@active_start_date = 0, 
												@active_end_date = 0, 
												@publisher_security_mode = ' + @PublisherSecurityMode + ',
												@publisher_login = @PublisherLogin,
												@publisher_password = @PublisherPwd,
												@job_login = @AgentLogin, 
												@job_password = @AgentPwd

			
				SET @Msg = @NL + ''Security Credentials used at runtime for publication: ' + @PublicationName + ''' + @NL +
                     ''@publisher_security_mode: ' + @PublisherSecurityMode + ''' + @NL + 
							''@publisher_login: '' + COALESCE(@PublisherLogin, @AgentLogin, ''SQLAgentServiceAccount'') + @NL + 
							''@publisher_password: '' + COALESCE(@PublisherPwd, @AgentPwd, ''SQLAgentServiceAccount'') + @NL + 
							''@job_login: '' + COALESCE(@AgentLogin, ''SQLAgentServiceAccount'') + @NL + 
							''@job_password: '' + COALESCE(@AgentPwd, ''SQLAgentServiceAccount'')

				RAISERROR(@Msg, 0, 1) WITH NOWAIT

				SET @Msg = @NL + @NL + ''New publication "'' + @PublicationName + ''" configured.''						
		END 
		ELSE -- Publication already found - inform user
		BEGIN
				SET @Msg = ''Publication "'' + @PublicationName + ''" already exists - please manually remove before running this script.''
		END
		RAISERROR(@Msg, 0, 1)						'
								)
		SET @PubCounter = @PubCounter + 1

	END