/***********************************************************************
	Create a new database for the test
***********************************************************************/
USE master
GO

IF DB_ID('HeapTest') IS NOT NULL
	DROP DATABASE HeapTest
GO

CREATE DATABASE HeapTest
GO

IF DB_ID('HeapTestRepl') IS NOT NULL
	DROP DATABASE HeapTestRepl
GO

CREATE DATABASE HeapTestRepl
GO

USE HeapTest
GO

/**********************************************************************
	Create a heaps with fixed columns sized such that
	only 4 rows can fit onto a page. 
***********************************************************************/
CREATE TABLE  dbo.Heap
(
	KeyField		CHAR(100)		NOT NULL CONSTRAINT PK_Heap PRIMARY KEY NONCLUSTERED,
	Filler			CHAR(1900)		NOT NULL
)
GO

USE master
EXEC sp_adddistributor @distributor = N'CARILLONPC', @password = N''
GO

EXEC sp_adddistributiondb @database = N'distributiondb', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

USE [distributiondb] 
IF (NOT EXISTS (SELECT * FROM sysobjects WHERE [name] = 'UIProperties' AND [type] = 'U ')) 
	CREATE TABLE UIProperties(id int) 
IF (EXISTS (SELECT * FROM ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ReplData', 'user', dbo, 'table', 'UIProperties' 
ELSE 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

EXEC sp_adddistpublisher @publisher = N'CARILLONPC', @distribution_db = N'distributiondb', @security_mode = 1, @working_directory = N'C:\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

USE [HeapTest]
EXEC sp_replicationdboption @dbname = N'HeapTest', @optname = N'publish', @value = N'true'
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'HeapTransRepl', @description = N'Transactional publication of database ''HeapTest'' from Publisher ''CARILLONPC''.', @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'true', @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'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'HeapTransRepl', @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

EXEC sp_addarticle @publication = N'HeapTransRepl', @article = N'Heap', @source_owner = N'dbo', @source_object = N'Heap', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Heap', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboHeap', @del_cmd = N'CALL sp_MSdel_dboHeap', @upd_cmd = N'SCALL sp_MSupd_dboHeap'
GO

--Add a push subscription to a transactional publication.
EXEC sp_addsubscription 
  @publication = N'HeapTransRepl', 
  @subscriber = N'CARILLONPC', 
  @destination_db = N'HeapTestRepl', 
  @subscription_type = N'push';
GO

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent 
  @publication = N'HeapTransRepl', 
  @subscriber = N'CARILLONPC', 
  @subscriber_db = N'HeapTestRepl'
GO

-- Start the publication snapshot (this should be quick!)
EXEC sp_startpublication_snapshot   @publication = N'HeapTransRepl'
GO 

INSERT INTO dbo.Heap (KeyField, Filler) 
VALUES ('1', REPLICATE('A',1900)), ('2', REPLICATE('B',1900)), ('3', REPLICATE('C',1900)), ('4', REPLICATE('D',1900)) ;
GO

DECLARE @db_id INT = DB_ID() ;
DBCC IND (@db_id, 'dbo.Heap', -1)
GO

DBCC TRACEON(3604)
DECLARE @db_id INT = DB_ID() ;
DBCC PAGE ( @db_id, 1, 209, 1 )
GO

USE [HeapTestRepl]
GO

-- Check the rows in the table
SELECT * FROM dbo.Heap 
GO

DECLARE @db_id INT = DB_ID() ;
DBCC IND (@db_id, 'dbo.Heap', -1)
GO

DECLARE @db_id INT = DB_ID() ;
DBCC PAGE ( @db_id, 1, 175, 1 )
DBCC PAGE ( @db_id, 1, 195, 1 )
GO

/************************************************************************************
Tidy Up
************************************************************************************/
-- Dropping the transactional articles
USE [HeapTest]
EXEC sp_dropsubscription @publication = N'HeapTransRepl', @article = N'Heap', @subscriber = N'all', @destination_db = N'all'
GO

EXEC sp_droparticle @publication = N'HeapTransRepl', @article = N'Heap', @force_invalidate_snapshot = 1
GO

-- Dropping the transactional publication
EXEC sp_droppublication @publication = N'HeapTransRepl'
GO

EXEC sp_replicationdboption @dbname = N'HeapTest', @optname = N'publish', @value = N'False'
GO

-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sys.sp_dropsubscriber	@subscriber = 'all'
GO

EXEC sp_dropdistpublisher @publisher = N'CARILLONPC';
GO

-- Delete the distribution database.
EXEC sp_dropdistributiondb @database = N'distributiondb';
GO

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO


IF DB_ID('HeapTestRepl') IS NOT NULL
	DROP DATABASE HeapTestRepl
GO

IF DB_ID('HeapTest') IS NOT NULL
	DROP DATABASE HeapTest
GO

DBCC TRACEOFF(3604)
GO