S94EGcgq

1--Set master database context
2use [master]
3GO
4
5--Add the awssct login to the sysadmin server role - required for replication
6ALTER SERVER ROLE [sysadmin] ADD MEMBER [awssct]
7GO
8
9--Set the recovery model to full for dms_sample_baseball - required for replication
10ALTER DATABASE [dms_sample_baseball] SET RECOVERY FULL WITH NO_WAIT
11GO
12
13--Set the recovery model to full for dms_sample_football - required for replication
14ALTER DATABASE [dms_sample_football] SET RECOVERY FULL WITH NO_WAIT
15GO
16
17--Configure this SQL Server as its own distributor
18exec sp_adddistributor @distributor = @@SERVERNAME, @password = N'Password1'
19exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
20GO
21
22--Change context to the distribution database
23use [distribution] 
24GO
25
26--Configure replication
27if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
28	create table UIProperties(id int) 
29
30if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
31	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
32else 
33	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
34GO
35
36exec sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
37GO