ÿþ/*************************************************************************************

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