Replicating MSSQL Server views
Since Microsoft SQL Server 2005 there is an option to replicate views to a table on a subscriber database. This is very usefull when you want to replicate data with an advanced filter. To replicate those views you have to make sure that the views commited to a few demands:
- Verify the setting of ANSI_NULLS and QUOTED_IDENTIFIER is correct.
- Verify the view definition is deterministic.
- Create the view using the WITH SCHEMABINDING option.
- Verify your session's SET options are set correctly as shown in the table below before creating the unique clustered index on the view.
- Create the unique clustered index on the view.
The COLUMNPROPERTY function can be used to check the value of IsPrecise and IsDeterministic on an existing table or view with the sample code:
SELECT object_id('<viewname>'), COLUMNPROPERTY(object_id('<viewname>'),'<columnname>','IsPrecise')
go
SELECT object_id('<viewname>'), COLUMNPROPERTY(object_id('<viewname>'),'<columnname>','IsDeterministic')
go
|
Than you can alter your views to make the bound to the schema with
ALTER VIEW [dbo].[viewname] WITH SCHEMABINDING
AS
SELECT [column 1], [column 2] FROM dbo.tablename
go |
After that you can place your index on the query with
|
CREATE UNIQUE CLUSTERED INDEX viewname_pk ON viewname ([Column 1])
go
|
With those things done you are ready to setup replication. The most easy way is to follow the wizard but do not execute the script. Just save the script to execute is manually because you have to make some changes in it. You have to change "indexed view schema only" to "Indexed View logbased" as in the sample below. Than your all done!
use [to_repl_db]
go
exec sp_addarticle @publication = N'Sample_publ', @article = N'viewname', @source_owner = N'dbo',
@source_object = N'v_rvdg', @type = N'indexed view schema only', @description = null, @creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'viewname', @destination_owner = N'dbo'
GO
|
exec sp_addarticle @publication = N'Sample_publ', @article = N'viewname', @source_owner = N'dbo',
@source_object = N'viewname', @type = N'Indexed View logbased', @description = null, @creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'view_name', @destination_owner = N'dbo'
GO
|
|
