0

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.

I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between two different instances. Indexes on Primary table keep getting changed and I need the empty stage_table with all the updated indexes when I do the partition switch.

  • 1
    I'm sorry I don't understand what you're trying to do. "I need the empty stage_table with all the updated indexes" - what does this mean? Is staging empty or not? What data is it supposed to contain exactly? "partition switch" - What partition? From where? Switch with what and what for? It might be easier to understand your exact scenario if you posted example schema of your main & staging tables, and the process you're trying to get to work. – Mat May 1 '13 at 7:10
  • So we have Table_A and Tabl_A_Stage within the same database. I need to automate and only sync indexes from Table_A to Table_A_Stage. The stage table is part of a partition mgmt job, and i have all aligned indexes on Table_A and need to have same indexes on Stage table to do the switch – Amam May 1 '13 at 20:23
  • 1
    please edit your question to clarify it. I still don't understand what you're doing from your previous comment (but I don't know SQL Server well some maybe I'm missing something obvious). As I said, show an example of exactly what you want is the best way to clarify. – Mat May 2 '13 at 5:00
2

You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.

Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.

Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?

EDIT :

Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.

This utility provides a command line interface to: 1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table. The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.

  • Kin, I need to setup a partition mgmt job, to move data between one set of partition tables to their corresponding Stage tables and then drop indexes from the stage tables and recreate indexes on a diffrent filegroup and then plug in the stage tables to a differnt set of partition tables (which reside on differnt FG's) – Amam May 1 '13 at 20:17
  • @Amam I have edited my answer as you want to move data between different partitions, the Partition Management tool will be best suited. – Kin Shah May 1 '13 at 20:27
  • At this point any third party tool is ruled-out, hence the need for a way to script it. – Amam May 1 '13 at 21:54
  • @Amam if you see my edited answer, I have pointed out a link to SQL Server Partition Management Tool from Codeplex which will be the best fit for your scenario. – Kin Shah May 1 '13 at 23:24
0

Another option would be a DDL trigger, which can capture index operations.

USE your_database;
GO

CREATE TRIGGER [IndexEventAudit]
   ON DATABASE
   FOR ALTER_INDEX, CREATE_INDEX, DROP_INDEX
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @e XML = EVENTDATA();

   DECLARE @sql NVARCHAR(MAX) 
     = @e.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)');

   DECLARE @obj SYSNAME 
     = @e.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(128)')

   IF @obj = N'Primary_Table'
   BEGIN
     SET @sql = REPLACE(@sql, N'Primary_Table', N'Stage_Table');
     EXEC sp_executesql @sql;
   END
END
GO

There is a narrow failure case here; if you have an index on a column that has the table name embedded. E.g. this would fail:

CREATE INDEX foo ON dbo.Primary_Table(Primary_Table_id);

Because the replaced SQL would look like this:

CREATE INDEX foo ON dbo.Stage_Table(Stage_Table_id);
-------- wrong column name ---------^^^^^^^^^^^^^^

You may also want to add preventative measures to make sure other people don't try to change the indexes on stage_table directly, which can obviously mess with this approach (and other approaches, likely, too). Also note that you may need to extend this to capture things like constraints, and also that if there are schema changes to the primary table that aren't reflected in the secondary table, or if you have naming conflicts etc., you'll need to handle those somehow too. For example, someone could add a column to the primary table, then index it, and the index on the secondary would fail because the column change wasn't reflected there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.