none
Only primary key and clustered indexes are being replicated non-clustered indexes aren't being replicated

    Question

  • I'm using schema option 0x00000000000090F3 on every table article in my publication being used for transactional replication, serving 6 pull subscriptions.  This option includes each of these options:

    0x0000000000000001
    0x0000000000000002
    0x0000000000000010
    0x0000000000000020
    0x0000000000000040
    0x0000000000000080
    0x0000000000001000
    0x0000000000008000

    My understanding of BOL is that, since it includes 0x10 and 0x40 that all indexes on the table should be replicating to the subscribers.  But that isn't happening -- I'm only getting primary key indexes/constraints and clustered indexes.  Is there a reason the non-clustered indexes might not be replicating as well?  

    I have read a few posts here and there that indicate indexes don't get replicated, or that the sync option might be a factor.  What would cause this?

    Thursday, July 18, 2013 3:57 PM

Answers

  • According to this post http://blogs.msdn.com/b/repltalk/archive/2010/02/24/decrypting-schema-option-parameters-binary-value-for-a-transactional-replication-article.aspx

    - these are the schema options you have:

       **SCHEMA OPTIONS HERE ARE**  
    ---------------------------------------
    0x01 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
    0x02 - Generates the stored procedures that propagate changes for the article, if defined.
    0x10 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
    0x20 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
    0x40 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
    0x80 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.
    0x1000 - Replicates column-level collation
    0x8000 - This option is not valid for SQL Server 2005 Publishers

    So your nonclustered indexes really should be replicated.

    Can you post the schema of one of the problem tables where with indexes and tell use what version of SQL Server you are running?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, July 22, 2013 1:22 PM
    Moderator

All replies

  • Hello,

    By default, only clustered key indexes are replicated and nonclustered indexes are not replicated.
    we can change the default settings for articles on the Articles window to include nonclustered indexes: Set "Copy nonclustered Indexes" to true.

    However, when you have multiple subscriptions and you only need to re-initialize one particular subscription.Changing the property to true may causes the snapshot to be invalid and you need to re-initialize all the subscriptions.
    Reference:http://sqllearnings.com/2012/04/04/subscription-re-initialization-and-non-clustered-indexes/

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Monday, July 22, 2013 8:11 AM
    Moderator
  • According to this post http://blogs.msdn.com/b/repltalk/archive/2010/02/24/decrypting-schema-option-parameters-binary-value-for-a-transactional-replication-article.aspx

    - these are the schema options you have:

       **SCHEMA OPTIONS HERE ARE**  
    ---------------------------------------
    0x01 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
    0x02 - Generates the stored procedures that propagate changes for the article, if defined.
    0x10 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
    0x20 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
    0x40 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
    0x80 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.
    0x1000 - Replicates column-level collation
    0x8000 - This option is not valid for SQL Server 2005 Publishers

    So your nonclustered indexes really should be replicated.

    Can you post the schema of one of the problem tables where with indexes and tell use what version of SQL Server you are running?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, July 22, 2013 1:22 PM
    Moderator

  • Hi, thanks for taking the time to comment.  Later I discovered that not all of the subscribers were missing the same non-clustered indexes.

    We're publishing from SQL 2000, and the subscribers are all SQL 2008 R2.

    Fanny, I don't think the snapshot wouldn't have been invalidated in the manner you mentioned, because from inception (using sp_addarticle), all articles had the non-clustered index option set to True.  All of the subscriptions are re-initialized weekly, and all of the table articles have 'drop' for @pre_creation_cmd.  This morning, there are no missing indexes.

    So, I'm still searching for reasons for possible inconsistencies here.

    As a workaround last week, I wrote a PowerShell script using SMO that, for each subscribing server, compares indexes between publisher and subscriber and replicates the ones that are missing.


    Mark Davis, Business Intelligence Engineer Imagine! Print Solutions

    Monday, July 22, 2013 2:03 PM
  • Is there any update on this? I have the exact same issue:

    Sql Server 2012

    Transactional replication using sp_addarticle with @schema_option = 0xC1810F3 for tables (both sp_helparticle and the UI corroborate that the "Copy indexes over" is set)

    The subscriber only has the primary key.

    I tried playing in the UI - disabling the option, reenabling and recreating the snapshot. The result is the same.

    Tuesday, November 19, 2013 8:09 PM
  • This should be working  - this is what your schema options translate to:

    Generates object creation script

    Generates procs that propogate changes for the article
    Generates corresponding clustered index
    Converts UDT to base data types
    Create corresponding nonclustered indexes
    Replicate pk constraints
    Replicates column-level collation
    Replicates partition scheme for partitioned table
    Replicates partition scheme for partitioned index
    Replicates indexes on xml columns
    Creates schemas not present on subscriber

    Can you please post the schema of your table and all the indexes here?  Are you replicating to SQL 2012 as well?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, November 19, 2013 9:44 PM
    Moderator
  • I'm the original poster of this thread.  To update you and affirm what Hilary has been saying, it actually has been working for me as designed.  

    However, earlier I had inconsistent results where some subscribers had all of the indexes, and others did not.  Some on another thread or forum indicated that a possible reason for this might be an error (e.g., file system permissions, disk space, post-snapshot script failure) occurring when an initial snapshot is generated, or when it's applied.  I did in fact experience a number of issues when initializing one or more subscriptions; although since I eventually resolved those, and all the tables existed and had primary keys, and the source and destination tables were in sync, I assumed there weren't any problems.  Then I noticed missing indexes when query performance was suffering. 

    In case you have a need to repair this acutely, I am including a script below (which I cannot guarantee -- you'll need to review/edit it so it will work in your environment).  On the other hand, if you can reinitialize your subscriptions that are missing indexes, after ensuring each article has the correct schema option, that should also resolve it.

    #region TaskInitialization
    param (
        [string]$Environment = "Debug",
        [string]$ModulesPath = "c:\Source\ScheduledTasks\Util\Modules",
        [string]$ConfigPath = "c:\Source\ScheduledTasks\Config" 
    )
    
    #Clear the console for test purposes
    cls
    
    $Path = $(Split-Path -Parent $MyInvocation.MyCommand.Path)
    $CurrentFolder = $(Split-Path -Leaf $MyInvocation.MyCommand.Path)
    $ScriptName = [IO.Path]::Combine($(Split-Path -Leaf $Path), [IO.Path]::GetFileNameWithoutExtension($(Split-Path -Leaf $CurrentFolder)))
    
    #Add the modules directory to the PSModulePath environment variable
    $env:PSModulePath = $env:PSModulePath + ";" + $ModulesPath
    
    Import-Module NLog -Force
    Import-Module Config -Force
    Import-Module TaskInitialization -Force
    Initialize-CommonItems -ScriptName $ScriptName -Environment $Environment -ConfigPath $ConfigPath -ModulesPath $ModulesPath
    #endregion
    
    cls
    
    $Logger.Info("Exporting indexes...");
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    
    Import-Module Sql -Force
    
    function Replicate-Index {
    	param(
    		[Parameter(Mandatory=$true)]
    		[System.Data.SqlClient.SqlConnection]$SourceConnection,
    		[Parameter(Mandatory=$true)]
    		[System.Data.SqlClient.SqlConnection]$DestinationConnection,
    		[Parameter(Mandatory=$true)]
    		[string]$SourceTable,
    		[Parameter(Mandatory=$true)]
    		[string]$SourceDb,
    		[Parameter(Mandatory=$true)]
    		[string]$DestDb,
    		[string]$DestinationTable = $SourceTable,
    		[Parameter(Mandatory=$true)]
    		[string]$IndexName
    	)
    
    	$destServerName = $DestinationConnection.DataSource
    	
    	$Logger.Info("Replicating non-clustered index $IndexName on $DestDb.$DestinationTable to subscriber $destServerName.")
    	Write-output "Replicating non-clustered index $IndexName on $DestDb.$DestinationTable to subscriber $destServerName."
    	
    	$Source = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SourceConnection
    	$Destination = New-Object "Microsoft.SqlServer.Management.Smo.Server" $DestinationConnection
    
    	$srcTable = $Source.Databases[$SourceDb].Tables[$SourceTable]
    
    	#script the create
    	$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
    
    	$Scripter.Options.AnsiFile = $true
    	$Scripter.Options.IncludeDatabaseContext = $false
    	$Scripter.Options.IncludeIfNotExists = $true
    	$Scripter.Options.ScriptSchema = $true;
    	$Scripter.Server = $SourceConnection.DataSource
    	$Scripter.Options.SchemaQualify = $false
    	$Scripter.Options.NoCommandTerminator = $true
    	
    	$index = $srcTable.Indexes[$IndexName]
    	if ($index -ne $null)
    	{
    
    		$createSql = $Scripter.Script($index)
    		$Logger.Info("Executing SQL ${createSql}")
    		try
    		{
    			$Destination.Databases[$DestDb].executeNonQuery($createSql);
    			$Destination.Refresh()
    		}
    		catch [System.Exception]
    		{
    			$Logger.Info("There was an error replicating non-clustered index $IndexName on $DestinationTable to subscriber $destServerName.")
    		}
    	}
    	
    }
    
    $resultValue = $false
    $resultValue = Get-DatabasesOnline
    if($resultValue -ne $true) 
    { 
    	$logger.Info("Databases Off-line.  Skipping this execution.")
    	return 
    }
    
    trap 
    { 
      $logger.ErrorException("Error", $_.Exception)
      exit 1 
    } 
    
    
    $sourceConnection = New-Object System.Data.SqlClient.SqlConnection
    $sourceConnection.ConnectionString = "Data Source=PSISQL;Initial Catalog=PSDB_1;user id=__;password=______;"
    $sourceConnection.Open()
    
    
    #Get publication data
    $pubsSQL = @"
    select distinct 'psdb_1' src_db, srvname, dest_db
    from psdb_1.dbo.syssubscriptions sub
    inner join [master]..sysservers srv on srv.srvid = sub.srvid
    where dest_db <> 'psdb_1' and srvname = 'db02\Reporting'
    union
    select distinct 'psdb_2' src_db, srvname, dest_db
    from psdb_2.dbo.syssubscriptions sub
    inner join [master]..sysservers srv on srv.srvid = sub.srvid
    where dest_db <> 'psdb_2' and srvname = 'db02\Reporting'
    union
    select distinct 'Misc' src_db, srvname, dest_db
    from Misc.dbo.syssubscriptions sub
    inner join [master]..sysservers srv on srv.srvid = sub.srvid
    where dest_db <> 'Misc' and srvname = 'db02\Reporting'
    union
    select distinct 'Imagine' src_db, srvname, dest_db
    from Imagine.dbo.syssubscriptions sub
    inner join [master]..sysservers srv on srv.srvid = sub.srvid
    where dest_db <> 'Imagine' and srvname = 'db02\Reporting'
    order by src_db, srvname, dest_db
    "@
    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $pubsSQL
    $SqlCmd.Connection = $sourceConnection
    
    $results = $SqlCmd.ExecuteReader();
    $publications = New-Object System.Data.DataTable
    [Void]$publications.Load($results)
    
    foreach ($pubRow in $publications.Rows) {
    
    	$src_db = $pubRow["src_db"]
    	$target_srv_name = $pubRow["srvname"]
    	$dest_db = $pubRow["dest_db"]
    
    	$indexListSQL = @"
    	use [$dest_db]
    	select o2.*, t.name table_name, i.name index_name, t.object_id as table_object_id
    	from sys.indexes i 
    	inner join sys.tables t on t.object_id = i.object_id and t.name not like 'MS%'
    	inner join sys.objects o on o.object_id = t.object_id and o.type = 'U' 
    	right join 
    	(
    		select o.name, i.name index_name
    		from psisql.$src_db.dbo.sysobjects o 
    		inner join psisql.$src_db.dbo.sysindexes i on i.id = o.id
    		where o.type = 'U' and indid <> 0 
    		and i.name not like '_WA_Sys%' 
    		and i.name not like 'Statistic%'
    		and i.indid <> 255
    		and o.name in (select name from psisql.$src_db.dbo.sysarticles)
    		--order by o.name
    	) o2 on o2.name = t.name and o2.index_name = i.name
    	where o.name is null
    	order by o2.name, o2.index_name
    "@
    
    	$destinationConnection = New-Object System.Data.SqlClient.SqlConnection
    	$destinationConnection.ConnectionString = "Data Source=$target_srv_name;Initial Catalog=$dest_db;Integrated Security=SSPI;"
    	$destinationConnection.Open()
    
    	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $indexListSQL
    	$SqlCmd.Connection = $destinationConnection
    
    	$results = $SqlCmd.ExecuteReader();
    	$TableMappings = New-Object System.Data.DataTable
    	[Void]$TableMappings.Load($results)
    
    	foreach($resultRow in $TableMappings.Rows) {
    
    		$indexName = $resultRow["index_name"]
    		$TableName = $resultRow["name"]
    		
    		Replicate-Index `
    			-SourceConnection $sourceConnection `
    			-DestinationConnection $destinationConnection `
    			-SourceTable $TableName `
    			-SourceDb $src_db `
    			-DestDb $dest_db `
    			-IndexName $indexName `
    	}
    
    }


    Mark Davis, Business Intelligence Engineer Imagine! Print Solutions


    • Edited by DavisBizHQ Wednesday, November 20, 2013 7:38 PM
    Wednesday, November 20, 2013 7:36 PM
  • I can repro this using just SSMS to replicate my database. It works for other databases. The only difference I see is that in this DB the indexes are created with "CREATE INDEX" not with "ALTER TABLE".

    I read in the documentation that CREATE INDEX commands ran AFTER the replication is setup won't be replicated. Does this mean that even though the indexes already exist, they won't be replicated because they have been created using CREATE INDEX?

    Wednesday, November 20, 2013 10:26 PM
  • "Does this mean that even though the indexes already exist, they won't be replicated because they have been created using CREATE INDEX?"

    I don't think so.  I believe the documentation is saying that existing indexes get transferred to the subscriber as part of the schema scripts that are in the snapshot and applied to the subscriber at initialization.  Thereafter, indexes which are added to published tables are not replicated.


    Mark Davis, Business Intelligence Engineer Imagine! Print Solutions

    Thursday, November 21, 2013 1:19 AM