SQL Server Performance

switched partitions and mirroring

Discussion in 'SQL Server 2008 General DBA Questions' started by colinr, Aug 17, 2009.

  1. colinr New Member

    Switched partitions dont support replication but are they supported on mirrored databases, im guessing not but would like a definitive answer ?
  2. MohammedU New Member

    <P mce_keep="true">From BOL: <A href="http://msdn.microsoft.com/en-us/library/cc280940.aspx">http://msdn.microsoft.com/en-us/library/cc280940.aspx</A></P><SPAN><DIV class=MTPS_CollapsibleRegion><DIV class=CollapseRegionLink jQuery1250552339516="7"><!-- ApplyClick with current id --><IMG style="BORDER-RIGHT-WIDTH: 0px; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; VERTICAL-ALIGN: middle; BORDER-LEFT-WIDTH: 0px" class=LibC_o src="http://i.msdn.microsoft.com/Global/Images/clear.gif" jQuery1250552339516="3"> &nbsp;Replication Support for Partition Switching </DIV><DIV style="DISPLAY: block" class=MTPS_CollapsibleSection><A id=sectionToggle1><!----></A><P>One of the key benefits of table partitioning is the ability to quickly and efficiently move subsets of data between partitions. Data is moved by using the SWITCH PARTITION command. For more information, see <A id=ctl00_MTContentSelector1_mainContentContainer_ctl18_ctl00_ctl00 onclick="javascript:Track('ctl00_MTContentSelector1_mainContentContainer_ctl18_ctl00_contenthere|ctl00_MTContentSelector1_mainContentContainer_ctl18_ctl00_ctl00',this);" href="http://msdn.microsoft.com/en-us/library/ms191160.aspx"><FONT color=#0033cc>Transferring Data Efficiently by Using Partition Switching</FONT></A>. By default, when a table is enabled for replication, SWITCH PARTITION operations are blocked for the following reasons:</P><UL><LI>If data is moved into or out of a table that exists at the Publisher but does not exist at the Subscriber, the Publisher and Subscriber could become inconsistent with one another. This problem typically occurs when data is moved into or out of a staging table.<BR></LI><LI>If the Subscriber has a different definition for the partitioned table than the Publisher, the Distribution Agent will fail when it tries to apply changes at the Subscriber. This is also an issue for SQL Server 2000 Subscribers, because SQL Server 2000 does not support partitioned tables.<BR></LI></UL><P>Despite these potential issues, partition switching can be enabled for transactional replication. Before you enable partition switching, make sure that all tables that are involved in partition switching exist at the Publisher and Subscriber, and make sure that the table and partition definitions are the same.</P><P>Mirroring also supports partitioning tables...</P></DIV></DIV></SPAN>

Share This Page