MSSQL Subscription Library index alignment

Requirements such as:

　　

In the original schema, each Web server had a fixed access to a database server, resulting in inconsistent indexing on each database subscriber. The need now is to align all the indexes on the subscription base, in order to achieve high availability + load balancing. The reason is that some of the apps are inaccessible because of a hard drive failure in the subscription library.

The idea is simple, rough.

1. Extract all index information from the database

SELECT object_name(i.[object_id]) tblname, I.name index_name, i.index_id, I.type_desc Index_type, C.name colname, Ic.index       _column_id index_column_id, Ic.is_included_column is_included_column, I.is_unique, I.fill_factor , I.filter_definition intoDBNAME_INDEX_ALIGN_192_168_10_2_V1 fromsys.indexes I Left OUTER JOINSys.index_columns IC oni.index_id=ic.index_id andIc.[object_id] =I.[object_id]       Left OUTER JOINSys.[Columns]C onC.[object_id] =I.[object_id]  andc.column_id=ic.column_idORDER  by object_name(i.[object_id])

2. Match all indexes (this step is more time consuming, equivalent to re-tuning all libraries)

2.1, first use 192.168.10.2 with 192.168.10.3 to do matching, the index of two libraries to adjust to the same

2.2, then use 192.168.10.2 with 192.168.10.4 to do matching, the adjustment on the 192.168.10.2 at the same time adjust to 192.168.10.3.

2.3, if there are more subscription libraries, has been using 192.168.10.2来 as a matching object, the adjustments made on this database are updated on the more matched subscription database.

# Use the following code to match
SELECT *  from (SELECTIa.tblname Tblname, IA. Index_name, IA. ColName, IA.INDEX_COLUMN_ID, IA. Index_type, Ia.is_included_column, Ia.filter_definition, Ia2.tblname cmp_tblname, ia2. Index_name Cmp_index_name, Ia2. ColName cmp_colname, ia2.index_column_id cmp_index_column_id,ia2. Index_type Cmp_index_type, Ia2.is_included_column cmp_is_included_column, Ia2.filter_definition Cmp_filter_definiti on fromDBNAME_INDEX_ALIGN_192_168_10_2_V1 ia Full JOINDBNAME_INDEX_ALIGN_192_168_10_3_V1 ia2 onIa.tblname=Ia2.tblname andIa. Index_name=ia2. Index_name andIa. ColName=ia2. ColName andia.index_column_id=ia2.index_column_id andIa.is_included_column=ia2.is_included_column) TWHERE  ISNULL(T.tblname, T.cmp_tblname) not inch(' Index name ') and ISNULL(T.index_type, T.cmp_index_type)<> 'HEAP'ORDER  by ISNULL(T.tblname, T.cmp_tblname),ISNULL(T.index_name, T.cmp_index_name),ISNULL(t.index_column_id, t.cmp_index_column_id)

Note: This approach, while achieving relatively high availability and load balancing, has the following drawbacks

1, because the index must be aligned, so in some of the subscription library also established some may never access to the index, which will lead to the consumption of space and index updates;

Above, if there is mistake, please do not hesitate to correct me.

MSSQL Subscription Library index alignment