locked
Setting up Sliding Window partition management RRS feed

  • Question

  • I am trying to implement the Sliding Window partition management algorithm to age off month-old data from ~20 different tables in SQLServer 2008.  There is certainly a lot of instruction and discussion on the topic, but I still have the following questions...

    1. I have seen a couple of different statements that the ALTER TABLE with the SWITCH option can't be used with replicated tables.  We are using replication in the peer-to-peer, publish/subscribe approach.  Is there indeed an unyielding problem or do we just need to perform some extra steps  in order to also use partition SWITCHing (or has it become a non-issue)?

    2. We also have a fairly involved foreign key / referential integrity scheme setup.  Is this going to cause us grief with the SWITCH step?

    3. I want to run my process each day right after midnight.  Is a SQL Agent job the right/only option?  If I use a job and call the PowerShell script, what do I need to do for db login/access credentials?  Will the executing user already have permissions or do I end up having to include a valid username/pw in the script (undesirable)?

    thanks,

    Curtis.

    Friday, February 4, 2011 4:15 AM

Answers

  • Hi Curtis,

    I believe you can replicate partition tables with SQL Server 2008 if you enable it in the publication. Check: http://msdn.microsoft.com/en-us/library/cc280940.aspx

    As for #2 - you cannot switch partition in the case if table is referenced by another table(s) with physical FK constraints. If this is the case, you have to drop FK and use other methods to force referential integrity.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, February 7, 2011 6:39 PM
  • What I meant in question 3 was directed at access by the job to the database tables.  How do I ensure that the automated job process has the necessary permissions to manipulate the database tables (ie. execute the ALTER TABLE...SWITCH, etc).

    ALTER permission on the table is enough to switch partitions. You can configure a proxy account which has ALTER permission to the job, otherwise the job step will use SQL Server Agent service account if no proxy configured. For more information, please refer to http://msdn.microsoft.com/en-us/library/ms190273.aspx.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 9, 2011 9:38 AM
  • Apparently:

    Merge replication does not support the SWITCH PARTITION command of ALTER TABLE, but transactional replication does



    Sevengiants.com
    Wednesday, February 9, 2011 11:36 AM

All replies

  • 2) Yes , it will

    3) BOL says

     

    By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

    • SQLAgentUserRole
    • SQLAgentReaderRole
    • SQLAgentOperatorRole

    For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

    Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, February 6, 2011 7:27 AM
  • Thanks for the reply.

    What I meant in question 3 was directed at access by the job to the database tables.  How do I ensure that the automated job process has the necessary permissions to manipulate the database tables (ie. execute the ALTER TABLE...SWITCH, etc).

    Back to question 2...Could I expect correctly to reduce my grief by carefully planning the order in which I SWITCH the table partitions out -- ensuring that I process the tables that are referenced from prior to the tables that are referenced to?

    Can anyone else address the question of the restriction between replicated tables and the SWITCH command?

    thanks again,

    Curtis.

    Monday, February 7, 2011 5:58 PM
  • Hi Curtis,

    I believe you can replicate partition tables with SQL Server 2008 if you enable it in the publication. Check: http://msdn.microsoft.com/en-us/library/cc280940.aspx

    As for #2 - you cannot switch partition in the case if table is referenced by another table(s) with physical FK constraints. If this is the case, you have to drop FK and use other methods to force referential integrity.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, February 7, 2011 6:39 PM
  • >>>with physical FK constraints.

    Dima

    Constraint is a logical implementation :-)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 8, 2011 6:51 AM
  • What I meant in question 3 was directed at access by the job to the database tables.  How do I ensure that the automated job process has the necessary permissions to manipulate the database tables (ie. execute the ALTER TABLE...SWITCH, etc).

    ALTER permission on the table is enough to switch partitions. You can configure a proxy account which has ALTER permission to the job, otherwise the job step will use SQL Server Agent service account if no proxy configured. For more information, please refer to http://msdn.microsoft.com/en-us/library/ms190273.aspx.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 9, 2011 9:38 AM
  • Apparently:

    Merge replication does not support the SWITCH PARTITION command of ALTER TABLE, but transactional replication does



    Sevengiants.com
    Wednesday, February 9, 2011 11:36 AM