Replicating Indexed Views as tables, instead of replicating all base tables and then creating an indexed view against those base tables, allows for a smaller subset of data to be replicated over. If you replicate base tables and then create an indexed view, replication performance decreases. This is due to data needing to be inserted\updated\deleted on the indexed view, and replication performance will decrease to IO bottleneck. If base tables are replicated over, you should create a regular index instead of an Indexed View.
Assumption: The distribution database is set up, and you know how to create a publication\subscription.
Limitation of Indexed views:
- Names must be in a two-part format, and an object cannot reference itself.
- You cannot schema bind a view with a three-part name since it is invalid for schema binding (‘DBName.dbo.tablename’). In other words, you can’t reference a table in a database other than the indexed view was created in.
- You cannot create an indexed view without the keyword “WITH SCHEMABINDING.” However, you can create a regular view that has three-part names, but the view has to be an “INDEXED VIEW” for replication.
- As a best practice, try and avoid conditional joins. It can slow down performance due to bottlenecks in IO.
Setting up the prerequisites:
- Create an “INDEXED VIEW” while making sure the view has “WITH SCHEMABINDING” and a “UNIQUE CLUSTERED INDEX” created for it.
- Since the view is created as “WITH SCHEMABINDING,” no modifications can be done to the underlying column(s) in the table(s) associated to the view.
- The view itself cannot be altered since it is used as an article in a publication.
- Columns can be added to the underlying table(s), but the column itself cannot be altered unless that view is deleted.
- To modify a view that is being used as an article in a publication:
- First, delete the publication and subscription.
- Delete the view.
- Modify the column.
- Recreate the indexed view.
- Create one publisher restricted to only replicating that one Indexed View. You can add other articles to that publication. You do not want to add other tables or Indexed Views to this publication since it has to be deleted\recreated in order to add\delete\change columns to the view. A new snapshot will be created; you do not want to do that for all articles within the publication. Plus, you have to make modifications to the script before you can run it, otherwise, you will encounter the error, “Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.”
Steps to Replicating Indexed Views as Tables
These steps can be followed to create the first publication\subscription and add a new column to the indexed view. If you delete the publication\subscriber and then delete rows from the original table(s), that is part of the view.
Caution: On the subscriber, you cannot add extra columns to the table that is part of replication. Replication will fail and error out until you remove those new columns. However, you can create different or new Indexes on the table that do not exist on the publisher. If you kick off a reinitialize, all Indexes that were created against that table on the subscriber will have to be recreated manually if pre_creation_cmd has been set to drop which is the default. However, if you run the Snapshot Agent (as described below) the indexes you created on tables will not get dropped.
- Start the View Snapshot Agent Status
a. This step creates a new snapshot and pushes all changes to the subscriber. This will eliminate the need to delete the table, view, SP, etc. on the subscriber first after making a change.
b. OpenSQL Server Management Studio.
c. Right-click Replication, and then choose Launch Replication Monitor.
d. In Replication Monitor, expand My Publishers, expand the server, and then select the publication.
e. In the right pane, select the Agents tab to view the status of the Snapshot Agent.
2. Connect to the Publisher in Microsoft SQL Server Management Studio, and then expand the server node.
3. Expand the Replication folder, and then right-click the Local Publications folder.
4. Click New Publication.
5. Using the Wizard, create the new publication but do not run it. You want it to be scripted out to a file so you can make changes to it.
6. Open the script.
7. Make these modifications before running:
–search for job_login and job_password. It is recommended that you use a domain account. When scripting out the code, the password is NEVER included. You must add that in with ticks around it, i.e. N’CorrectPassword’.
@job_login = N’Domain\ID’,
@job_password = null
–Adding the transactional articles
–change this to logbased
@type = N’indexed view logbased’,
–Destination table can be a different name other than the view name.
@destination_table = N’IndexedViewName’ or ‘NewTableName’,
8. After making the necessary modifications, run it. If it errors out, fix the error(s), but delete the publication before re-running it.
9. Create the subscription.
10. Once everything is created and running correctly, script out the creation of the publisher (this will also script out the subscription) and keep it somewhere–or just remember to script it out if you need to drop and/or recreate it.
11. To make changes to the Indexed View (adding or removing columns), do the following:
a. Script out the publisher\subscriber (or open the script you created in step 6 as long as you know nothing has been modified since you scripted it out). If you re-script it out, make sure to add the password for @job_password (do a search and fix).
b. You should comment out exec sp_replicationdboption @dbname (first execute statement) since the database was already set to allow replication. This is not required; it will just tell you it was already set to “True”.
c. You should comment out exec DBName.sys.sp_addlogreader_agent (second execute statement) since that agent was already created the first time you created the publisher. If this is not done, it will error that the agent is already created. Not a big deal if you do not comment it out, just realize you will receive an error because it existed.
d. Drop the subscription (you want SQL to drop the subscription from the destination server) and then the publication for the indexed view only. Make sure the subscription is deleted on the destination server.
e. Script out the clustered index first, then script out the view and alter it. When you modify the view, the clustered index is deleted and it has to exist in order to recreate the publisher. Re-run the script to alter the view and run the script to recreate the clustered index.
f. Run the script to recreate the publisher\subscriber.
12. You do not need to drop the table on the subscriber. Just push the new changes to the subscriber.
13. Right click the Publication and click “View Snapshot Agent Status“.
a. Click Start and just note the window will show this new article is now synced.
b. The new articles will be synced to the subscriber.
c. Refresh the table on the subscriber and the columns and data will be there.