How to copy non-clustered indexes to subscriber in snapshot replication

In snapshot replication by default Non-cluster indexes are not copied to subscriber below is the work around for copying Non-cluster indexes to subscriber in Snapshot replication.

Step 1:

  - Login into the Publisher server

  -  Expand the Replication folder and Local Publications

  -  Right click on the publisher and select properties

Step 2:

                In Publication Properties window select Articles Tab à Select the Article Properties

               

Step 3:

  -  In Popup select Set Properties of All Table articles to copy all objects Non-Cluster indexes

  -  Select Set Properties of Highlighted table article to change the option to copy Non-cluster indexes of specific table

Step 4: 

                In Properties for All Tables Articles window change the below highlighted property from False to True.

               

In next run of snapshot will copy all noncluster indexes to subscriber.

Validate all views in the database

There is an sp that validates the metadata and the   same sp we can use to validate

set nocount on

select ' print ''' +name +'''

exec sp_refreshview ['+name+']' + CHAR(10)+ '

go '

from sys.views

Execute  the above script it will give script to run sp_refreshview on all views. Run the scripts generated using above sql statement I will give error link below screen

SQL Server and Build Numbers

Build chart lists all of the known Service Packs (SP)

	RTM	SP1	SP2	SP3	SP4
SQL Server 2014	12.0.2000.8 12.00.2000.8
SQL Server 2012	11.0.2100.60 11.00.2100.60	11.0.3000.0 or 11.1.3000.0	11.0.5058.0 or 11.2.5058.0
SQL Server 2008 R2	10.50.1600.1	10.50.2500.0/ 10.51.2500.0	10.50.4000.0/ 10.52.4000.0	10.50.6000.34/ 10.53.6000.34
SQL Server 2008	10.0.1600.22 10.00.1600.22	10.0.2531.0 10.00.2531.0 or 10.1.2531.0	10.0.4000.0 10.00.4000.0/ 10.2.4000.0	10.0.5500.0 10.00.5500.0/ 10.3.5500.0	10.0.6000.29 10.00.6000.29/ 10.4.6000.29
SQL Server 2005	9.0.1399.06 9.00.1399.06	9.0.2047/9.00.2047	9.0.3042/ 9.00.3042	9.0.4035/ 9.00.4035	9.0.5000/ 9.00.5000
SQL Server 2000	8.0.194 8.00.194	8.0.384 8.00.384	8.0.532 8.00.532	8.0.760/ 8.00.760	8.0.2039 8.00.2039
SQL Server 7.0	7.0.623 7.00.623	7.0.699 7.00.699	7.0.842 7.00.842	7.0.961 7.00.961	7.0.1063

DBCC CHECKIDENT

-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed

( ID INT IDENTITY(1,1) NOT NULL,

DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 

DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.

--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'

--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')

-- After deleting data identity value didn’t reset and the value will be remain same  

---  Output like :  Checking identity information: current identity value '20', current column value '20'.

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed