Create a Transactional Publication (without Snapshot)

To create a Publication in the Anthology Student database, do the following:

Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server instance (referred to as the Publisher) containing the Anthology Student database that you wish to replicate to another SQL Server Instance.

In the Object Explorer, select the (+) sign to expand the Replication folder.
Right-click on the Local Publications folder and select New Publication.
On the starting page of the New Publication wizard, select Next.
If this SQL Server instance is not configured as a Distributor (or as a Publisher with a remote Distributor), use the Distributor and Snapshot Folder pages of the New Publication Wizard to configure an instance for distribution (Step 5).

If the server is already configured as a Distributor, continue to Step 7.
On the Distributor page, select the “<Server Name> will act as its own Distributor” option to host the Distributor database on the Publisher SQL Server instance where the Publication is being configured.

Note: You may also wish to set up a dedicated box to be your distribution system if the current SQL Server instance is already under heavy load. In that case, you would have select the second option and add the target SQL Server to host the Distributor database.
On the Snapshot Folder page, type the path of a network share for the snapshot folder. Select Next.

For more information on how to create a snapshot folder, refer to the following Microsoft Technet articles:
- Creating Windows Accounts for Replication
- Replication Agent Security Model
- Preparing the Snapshot Folder
On the Publication Database page, select the Anthology Student database to be replicated (referred to as the Publication Database) and select Next.
On the Publication Type page, select Transactional publication and select Next.
On the Articles page, select the Tables check box to select all the tables in the database and select Next.

Note: Once the publication is created, a script needs to be executed on the publication database in order to exclude those tables that are not required for replication (Step 20). Refer to Non-Replicated Tables for a list of some of the Anthology Student database tables that are excluded from replication.
On the Filter Table Rows page, select Next. Row filtering will not be added to the Publication.
On the Snapshot Agent page, select Next.

Note: Ensure that all check boxes are cleared as you need to modify the Snapshot properties. You must manually start the Snapshot Agent after you make the changes.
On the Agent Security page, select Security Settings to launch the Snapshot Agent Security page.
On the Snapshot Agent Security page, select the first option “Run under the following Windows account” to run the Snapshot Agent using a Windows account.

In the Process account field, type a Windows user account under which the security agents will run. Then, type and confirm the password for the Windows user and select OK.
Notes:
- For more information about the permissions that are required for the Log Reader Agent, refer to the "Replication Agent Security Model" section in SQL Server Books Online.
- The Windows user name and password are not validated in this wizard. Any issues, such as insufficient permissions, will occur only after the Publication is created and the Log Reader Agent runs and tries to replicate the changes.
On the Agent Security page, select Security Settings to launch the Log Reader Agent Security page. Specify the same settings for the Log Reader Agent and select Next.
On the Wizard Actions page, select the Create the publication check box and select Next.
On the Complete the Wizard page, type the name of the Anthology Student database for the Publication.
Verify the displayed actions. Select Back to change the settings.
Select Finish.
On the Creating Publication page, wait until the wizard finishes and select Close.

Execute the script below on the Publication database to exclude tables not required for replication.


SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #DoNotReplicate (aid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName VARCHAR(255), SchemaName NVARCHAR(255) default ('dbo'))

SET NOCOUNT ON

BEGIN TRY
INSERT #DoNotReplicate (SchemaName, TableName)
SELECT SCHEMA_NAME(SCHEMA_ID) as SchemaName, NAME AS TableName 
FROM sys.tables 
WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') = 0 AND is_ms_shipped = 0
UNION
SELECT SCHEMA_NAME(SCHEMA_ID) as SchemaName, NAME AS TableName 
FROM sys.tables WHERE 
	( NAME LIKE '%work' OR NAME LIKE '%isir%work%'
		OR NAME LIKE '%nsldswork%' OR name LIKE '%isir%_EXT%' OR name LIKE '%WorkOrigExt%'
		OR NAME LIKE '%proxy%'   OR NAME LIKE 'syservice%'  OR name LIKE '%ExternalMap%'
		OR NAME LIKE 'syIMS%' OR NAME LIKE 'syQueue%' OR NAME LIKE '%tblPerson%' 
		OR NAME LIKE '%Queue'
		OR NAME LIKE 'SyPerson_Stag' OR NAME LIKE 'SyPersonUnique_Stag' 
		OR NAME LIKE 'NetSQLAzman%'
		OR NAME LIKE 'tblStagSyPersonPhoneNumber' OR NAME LIKE 'SyPerson_Stag_Delta'
		OR NAME LIKE 'Sa1098TBatchToolDetails'
		OR Name LIKE 'FaFISAPReportDetails')
UNION
SELECT SCHEMA_NAME(SCHEMA_ID) as SchemaName, NAME AS TableName FROM sys.tables WHERE schema_name(schema_id) = 'core'

insert #DoNotReplicate (TableName) values ('WebPrivacyPolicyDetail'), ('webCampusScream'), ('webItems'), ('webPages'), ('WebPrivacyPolicy'),('SyScriptRunStats') , ('syInQueue'), ('syMailInboxQueue'), ('syQueueAudit'), ('webStyles'), ('FaB2BclFileSys'),('SyVersionInfo'), ('SyVersion'), ('SyRemovedFromRepl'),('FaShoppingSheetConsumer'), 
('SyReplTableForSnaphotReplicationWork'), ('SySSBConversations')
 
DECLARE @id INT, @TableName NVARCHAR(255), @tSQL NVARCHAR(4000), @SchemaName NVARCHAR(255), @IsNewVersion BIT
SELECT @id = 0, @tSQL = ''

SELECT TOP 1 @id = aid, @TableName = TableName , @SchemaName = SchemaName
	FROM #DoNotReplicate  WHERE aid > @id ORDER BY aid

SELECT @IsNewVersion = CASE WHEN EXISTS (SELECT 1 
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
where SO.name like 'cmc_PrepTableForAlter'
	and P.name = '@SchemaName') THEN 1 ELSE 0 END

WHILE (@@ROWCOUNT > 0)
BEGIN
	SET @tSQL = N'EXEC dbo.cmc_preptableforalter @TableName = ''' + @TableName + ''', @action = ''drop''' + CASE WHEN @IsNewVersion = 1 THEN ', @SchemaName = ''' + @SchemaName + '''' ELSE '' END 
	PRINT N'Excluding ' + @TableName 
	EXEC sys.sp_executesql @tSQL
	--PRINT @tSQL

	SELECT TOP 1 @id = aid, @TableName = TableName , @SchemaName = SchemaName
	FROM #DoNotReplicate  WHERE aid > @id ORDER BY aid 
END

DROP TABLE #DoNotReplicate
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.SyIndexRecreatePostUpgradeWork'))
AND EXISTS (SELECT 1 FROM sys.columns WHERE is_identity = 1
AND COLUMNPROPERTY(OBJECT_ID('dbo.SyIndexRecreatePostUpgradeWork'), 'SyIndexRecreatePostUpgradeWorkID', 'IsIdNotForRepl') = 1
)
BEGIN
	ALTER TABLE dbo.SyIndexRecreatePostUpgradeWork ALTER COLUMN SyIndexRecreatePostUpgradeWorkID DROP NOT FOR REPLICATION
END

SET NOCOUNT OFF
GO