use master
GO

-- Add GSDR User for synchronization
sp_addlogin @loginame = 'GSDR', @passwd = 'GSDR'
GO
sp_addsrvrolemember @loginame = 'GSDR', @rolename = 'sysadmin' 
GO

exec sp_adddistributor @distributor = @@servername
-- @password = N'GSDR'
GO

-- Adding the distribution database
exec sp_adddistributiondb  @database = N'distribution', @data_file = N'distdb.mdf', @data_file_size = 10, @log_file = N'distlog.ldf', @log_file_size = 300, @min_distretention = 0, @max_distretention = 9999, @history_retention = 24, @security_mode = 0, @login = N'sa', @password = N''
-- @data_folder = N'%MSSQLDATA%' 
-- @log_folder = N'%MSSQLDATA%'
GO

-- Adding the distribution publisher
exec sp_adddistpublisher  @publisher = @@servername, @distribution_db = N'distribution', @security_mode = 1, @trusted = N'false', @thirdparty_flag = 0
-- @working_directory = N'\\%COMPUTERNAME%\%MSSQLSHARE%\REPLDATA' 
GO

-- Enabling the replication database
use master
GO

exec sp_replicationdboption N'GlobalSTORE', N'publish', N'true'
GO

use [GlobalSTORE]
GO

exec sp_addpublication @publication = N'IN_STORE', @restricted = N'false', @sync_method = N'character', @repl_freq = N'continuous', @description = N'In store replication', @status = N'active', @allow_push = N'true', @allow_pull = N'false', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 72, @enabled_for_het_sub = N'true'


exec sp_addpublication_snapshot @publication = N'IN_STORE',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959
GO

--exec sp_grant_publication_access @publication = N'IN_STORE', @login = N'BUILTIN\Administrators'
--GO
exec sp_grant_publication_access @publication = N'IN_STORE', @login = N'distributor_admin'
GO
--exec sp_grant_publication_access @publication = N'IN_STORE', @login = N'GSDR\Administrator'
--GO
exec sp_grant_publication_access @publication = N'IN_STORE', @login = N'sa'
GO

-- Adding the transactional articles
exec sp_addarticle @publication = N'IN_STORE', @article = N'PLU', @source_owner = N'dbo', @source_object = N'PLU', @destination_table = N'PLU', @type = N'logbased'/*, @creation_script = N'PLU.sch'*/, @description = N'PLU', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000000, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null
GO


exec sp_addsubscription @publication = N'IN_STORE', @subscriber = N'TEST1', @destination_db = N'tmp.mdb', @subscription_type = N'push', @subscriber_type = 3
Go


EXEC sp_addpushsubscription_agent 
  @publication = N'IN_STORE', 
  @subscriber = N'TEST1', 
  @subscriber_db = N'tmp.mdb' 
GO


-- set all related jobs to log to event log upon failure
use msdb
GO

declare @jobname varchar(255)
declare jobnames cursor for select name from sysjobs where category_id in (10, 11, 12, 13, 15, 16, 17, 18)

open jobnames
fetch next from jobnames into @jobname

while (@@fetch_status <> -1)
begin
   if (@@fetch_status <> -2)
   begin
      execute ('sp_update_job @job_name = ''' + @jobname + ''', @notify_level_eventlog = 2')
   end
   fetch next from jobnames into @jobname
end
close jobnames
deallocate jobnames