Step 1. After you've provided the @publication_db, @subscriber, @destination_db, and @publication variable names, the stored procedure collects a string of destination table names (in @article_list) for subscribed articles in the publication by using a cursor to loop through all the names in the replication system tables. The code in Listing 1 creates the cursor. (Listing 1 is a snippet of the complete stored procedure—hub_remote_drop_save_subscribed_article_indexes.sql—which you can download from the Web.) To collect destination table names from the sysarticles system table, the stored procedure first has to find the subscribed article IDs and the publication ID. Note that this code takes into account the possibility that subscribers might not always subscribe to all articles in a publication. The subscribed article IDs in the syssubscriptions system table are identified by the combination of the subscriber's server ID (srvid) plus @destination_db. You can access srvid from the sysservers system table when you provide the subscriber's name, and you can retrieve the publication ID from the syspublications system table when you provide the publication's name. (For more information about using system tables, see Kalen Delaney's series of Inside SQL Server columns: "Accessing System Tables," March 2000, InstantDoc ID 8033; "Property Functions, Schema Views," April 2000, InstantDoc ID 8238; and "Direct Access," May 2000, InstantDoc ID 8415.
Step 2. Using the target-table names string, from the distributor, the stored procedure calls the remote procedure hub_drop_save_
article_indexes (stored in the subscriber) to drop and save indexes and constraints for target tables at the subscriber, as Listing 1 shows. Note that to allow a remote stored-procedure call, you have to join the distributor and the subscriber to each other as linked servers by giving them data-access and remote-login permissions. To fulfill those requirements, in Query Analyzer you can execute the stored procedures that Listing 2 shows. For details about using these system stored procedures to add linked servers, see sp_addlinkedserver under "Transact-SQL Reference" in BOL.
Step 3. At the subscriber, the remote stored procedure uses a COM object, which calls the SQL-DMO API to drop indexes and key constraints on the destination tables and save their recreation scripts. I recommend using SQL-DMO objects for several reasons. SQL scripting—including scripting for index or key constraint creation—can become complex, so using SQL Server's script-generation functionality is better than manually creating the scripts through T-SQL code. Enterprise Manager lets you generate DROP, CREATE INDEX, and key constraint scripts for the set of destination tables by right-clicking a subscriber's table, selecting All Tasks, then selecting Generate SQL Script. However, any change to indexes, key constraints, articles, and the subscription makes the scripts obsolete.
SQL-DMO provides one Script method for almost every object. The Script method returns a SQL statement that you can use to recreate the referenced object. After the drop step has saved all the scripts of existing indexes and key constraints for destination tables in a stored procedure at the subscriber, the recreate step of the corresponding Distribution Agent job can execute remotely.
Step 4. To facilitate using SQL-DMO objects and their Script methods in the remote stored procedure, you can encapsulate all the SQL-DMO code in a custom COM component. This component exposes a simple interface method that the stored procedure can call. The method drives all the dropping and scripting. I named this component Hubindex, the interface TableIndex, and the method Drop_Save_Table_Indexes. I also implemented this component as an in-process server that runs within the same memory space as that of the stored procedure. In Visual Basic (VB), you create this component by using an ActiveX DLL project, and you must check the reference to the SQL-DMO Object Library.
SQL Server provides OLE Automation stored procedures such as sp_OACreate to create a COM object and sp_OAMethod to call its method. Listing 3 shows how I used those OLE Automation stored procedures in the remote stored procedure. (Listing 3 is a snippet of the complete remote stored procedure—hub_drop_save_article_indexes.sql—which you can download from the Web.) Given HubIndex.TableIndex, the programmatic identifier (ProgID) of an OLE object, sp_OACreate creates the object and outputs an object token that identifies this object in T-SQL. You must pass this object token when you use sp_OAMethod to call the Drop_Save_Table_Indexes method of this object. For a script that shows you how to use OLE Automation stored procedures, see the "OLE Automation Sample Script" topic in BOL.
The Drop_Save_Table_Indexes method in turn calls the Script methods of SQL-DMO objects and saves the creation scripts in a programmatically created stored procedure (part of whose name is @publication), which stores them in @destination_db. For example, given a publication named addressing_truncate, the Drop_Save_Table_Indexes method would programmatically create a stored procedure named create_addressing_truncate_indexes. By naming the programmatically created stored procedure this way, you can distinguish among multiple stored procedures within the same subscription (destination) database. A safeguard for using this drop (destructive) method is the boolean flag @is_drop, which saves the scripts without dropping (destructing) the corresponding indexes and key constraints if it's set to 0 (false). This method is general enough for dropping and saving the scripts of indexes and key constraints for any list of tables.
Note that the custom COM component, the remote stored procedure, and the target tables are all local at the subscriber. The actual dropping and saving of the indexes and keys occur at the subscriber without a round-trip back to the distributor until the remote procedure finishes. For a detailed description of the drop-and-save process and a VB code snippet of the VB COM object's Drop_Save_Table_Indexes method, see the Web sidebar "Stop, Drop, and Save," which includes Web Listing A, at http://www.sqlmag.com, InstantDoc ID 25694. Web Listing A is a snippet of the Drop_Save_Table_Indexes method, part of the complete executable DMO_drop_save_indexes_listing.zip VB project that implements this method.
Prev. page
1
2
[3]
4
next page 
