{"id":39799,"date":"2025-07-31T21:02:32","date_gmt":"2025-07-31T19:02:32","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=39799"},"modified":"2025-08-07T18:09:03","modified_gmt":"2025-08-07T16:09:03","slug":"customer-case-study-partition-switching-and-transactional-replication","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/","title":{"rendered":"Customer case study &#8211; Partition switching and transactional replication"},"content":{"rendered":"\n<p>The client\u2019s environment includes two SQL Server instances (I1 and I2). One of the databases, D1 (on I1), is replicated to I2 via transactional replication. Additionally, the tables in this database are partitioned. The largest table (which is the one of interest) is approximately 350 GB in size and contains around 800 million rows. This table represents transactions.<\/p>\n\n\n\n<p>The client has submitted the following requirements:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>They want to archive data with minimal impact on data availability (during the archiving process)<\/li>\n\n\n\n<li>They want to be able to retain the archived data<\/li>\n\n\n\n<li>They want to be able to make the archived data accessible or make it visible again<\/li>\n<\/ul>\n\n\n\n<p>The following elements also need to be taken into account:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transactional replication is enabled on this table<\/li>\n\n\n\n<li>This table is partitioned<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-what-does-the-client-s-environment-look-like\"><strong>What does the client&#8217;s environment look like?<\/strong><\/h3>\n\n\n\n<p>The client\u2019s environment consists of the following elements:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A SQL Server 2019 Standard Edition instance (Server 1) \u2013 Publisher<\/li>\n\n\n\n<li>A SQL Server 2019 Standard Edition instance (Server 2) \u2013 Subscriber<\/li>\n\n\n\n<li>A database composed of several tables, including one table that represents transactions<\/li>\n\n\n\n<li>Transactional replication is used to replicate certain tables between the two SQL Server instances<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"848\" height=\"418\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\" alt=\"\" class=\"wp-image-39807\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png 848w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41-300x148.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41-768x379.png 768w\" sizes=\"auto, (max-width: 848px) 100vw, 848px\" \/><\/figure>\n\n\n\n<p>The table we are interested in consists of several columns and is partitioned by quarter (based on the Transaction Date column):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"556\" height=\"710\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-42.png\" alt=\"\" class=\"wp-image-39808\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-42.png 556w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-42-235x300.png 235w\" sizes=\"auto, (max-width: 556px) 100vw, 556px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"626\" height=\"687\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-43.png\" alt=\"\" class=\"wp-image-39809\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-43.png 626w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-43-273x300.png 273w\" sizes=\"auto, (max-width: 626px) 100vw, 626px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-what-are-the-possible-solutions\"><strong>What are the possible solutions?<\/strong><\/h3>\n\n\n\n<p>One possible solution for archiving data (while keeping it easily accessible) is partition switching. It is indeed possible to &#8220;move&#8221; data from one partition to another. To do this, an identical table must be created. In our case, it must have the same number of partitions.<\/p>\n\n\n\n<p>We will therefore need to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a &#8220;copy&#8221; or &#8220;archive&#8221; table that will contain the archived data from the transactions table<\/li>\n\n\n\n<li>Since we are using transactional replication, the ALTER SWITCH command must be accounted for and replicated in such a way that the subscriber also executes the ALTER SWITCH command<\/li>\n<\/ul>\n\n\n\n<p>The command looks like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE transactions SWITCH PARTITION 2 TO transactions_archive PARTITION 2;<\/code><\/pre>\n\n\n\n<p>There are many advantages:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data &#8220;movement&#8221; using the ALTER SWITCH command is almost instantaneous, even when millions of rows are involved<\/li>\n\n\n\n<li>The command is simple to implement<\/li>\n\n\n\n<li>A rollback is possible, meaning data can be moved from one partition to another, or from one table to another. The \u201cmovement\u201d can occur in both directions:\n<ul class=\"wp-block-list\">\n<li>From the transactions table to the archive_transactions table<\/li>\n\n\n\n<li>From the archive_transactions table back to the transactions table<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Data remains easily accessible since it is only moved to another table<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-what-does-it-look-like\"><strong>What does it look like?<\/strong><\/h3>\n\n\n\n<p>The data from the selected partition is &#8220;migrated&#8221; to the same partition in the destination table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"482\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45-1024x482.png\" alt=\"\" class=\"wp-image-39814\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45-1024x482.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45-300x141.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45-768x362.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45-1536x723.png 1536w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-45.png 1544w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>Problems that arise:<\/strong><\/p>\n\n\n\n<p>By default, it is not possible to perform partition switches when transactional replication is being used. You can verify this using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_helppublication @publication = N'Publication_MyTransactions';\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"37\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-46-1024x37.png\" alt=\"\" class=\"wp-image-39816\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-46-1024x37.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-46-300x11.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-46-768x28.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-46.png 1374w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>It is therefore necessary to allow the publication to handle &#8220;ALTER SWITCH&#8221; commands so that the partition switch is replicated to the subscriber. This is done using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_changepublication\n\t@publication = N'Publication_MyTransactions',\n\t@property = N'allow_partition_switch',\n\t@value = N'true'\ngo\n\nexec sp_changepublication\n\t@publication = N'Publication_MyTransactions',\n\t@property = N'replicate_partition_switch',\n\t@value = N'true'\ngo<\/code><\/pre>\n\n\n\n<p>In our client&#8217;s case, the indexes were not aligned. In other words, they were not partitioned. When this happens, it is not possible to perform a partition switch. More generally, the source and destination tables must have exactly the same structure.<\/p>\n\n\n\n<p>From there, we have two issues:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The unaligned indexes need to be rebuilt (and partitioned)<\/li>\n\n\n\n<li>The new table (transactions_archive) needs to be added to the publication<\/li>\n<\/ul>\n\n\n\n<p>Once these two actions are completed, a new snapshot must be generated. When this occurs, all articles are taken into account, and the snapshot captures all of them. The issue is that our client\u2019s publication includes several dozen tables with a total volume exceeding 1 TB.<\/p>\n\n\n\n<p>To include only the modified articles (in our specific case), the transactions table must be removed from the publication, the necessary changes must be made, and then the table should be added back (along with the transactions_archive table). Some publication properties must also be modified. This is done using the commands below:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_helppublication @publication = N'Publication_MyTransactions'\ngo\n\nexec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'allow_anonymous', \n\t@value = 'false'\ngo\n\nexec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'immediate_sync', \n\t@value = 'false'\ngo\n<\/code><\/pre>\n\n\n\n<p>Here are the different steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enable partition switching since we are using transactional replication<\/li>\n\n\n\n<li>Generate a snapshot only for the modified articles<\/li>\n\n\n\n<li>Remove the transactions article from replication<\/li>\n\n\n\n<li>Rebuild the non-partitioned indexes on the transactions table<\/li>\n\n\n\n<li>Create the transactions_archive table with the same structure as the transactions table<\/li>\n\n\n\n<li>Add the transactions and transactions_archive articles to the publication\n<ul class=\"wp-block-list\">\n<li>When adding them, consider the clustered and nonclustered indexes as well as the partitioning<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"358\" height=\"459\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-47.png\" alt=\"\" class=\"wp-image-39819\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-47.png 358w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-47-234x300.png 234w\" sizes=\"auto, (max-width: 358px) 100vw, 358px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Start the snapshot agent<\/li>\n\n\n\n<li>Execute a partition switch and verify the result<\/li>\n\n\n\n<li>Plan for a rollback of the allow_anonymous and immediate_sync properties<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-implementation\"><strong>Implementation<\/strong><\/h3>\n\n\n\n<p>We previously mentioned that the transactions table already existed and was being replicated (via transactional replication).<\/p>\n\n\n\n<p>We therefore have our database (MyTransactions) with several filegroups (to simplify, we are only creating a few filegroups that will contain the data for 2024):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>use master\ngo\n\ndeclare @NumPart int = 1, @NumPartStr varchar(2), @SQLCmd nvarchar(max) = ''\nwhile (@NumPart &lt;= 4)\nbegin\nselect @NumPart\nset @NumPartStr = +RIGHT('0'+CAST(@NumPart as varchar(2)), 2)\nset @SQLCmd = @SQLCmd+'alter database MyTransactions add filegroup FG2024Q'+@NumPartStr+'; '\nset @SQLCmd = @SQLCmd+'alter database MyTransactions add file (name = F2024Q'+@NumPartStr+', filename = ''D:\\Data\\F2024Q'+@NumPartStr+'.mdf'') to filegroup FG2024Q'+@NumPartStr+';'\nset @NumPart = @NumPart+1\nend\nprint @SQLCMD\nexec(@SQLCMD)<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"507\" height=\"331\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-48.png\" alt=\"\" class=\"wp-image-39820\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-48.png 507w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-48-300x196.png 300w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/figure>\n\n\n\n<p>We create our partition function:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>use MyTransactions \ngo\n\ncreate partition function FunctionPartition_Transactions(DATE)\nas range right for values ('20240101','20240401','20240701','20241001')\ngo<\/code><\/pre>\n\n\n\n<p>We create our partition scheme:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>use MyTransactions \ngo\n\ncreate partition scheme SchemePartition_Transactions as partition FunctionPartition_Transactions\nto (&#091;Primary], FG2024Q01, FG2024Q02, FG2024Q03, FG2024Q04)\ngo<\/code><\/pre>\n\n\n\n<p>We then create our transactions table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>use MyTransactions \ngo\n\ncreate table transactions\n(\n\ttransactions_id int identity(1,1) not null,\n\ttransactions_label nvarchar(50) not null,\n\ttransactions_amount int not null,\n\ttransactions_date date not null\n) on SchemePartition_Transactions(transactions_date)\n\nalter table transactions add constraint pk_transactions primary key clustered(transactions_id, transactions_date)\ngo\n\ncreate nonclustered index ix_transactions_label_date on transactions(transactions_label) include (transactions_date) on &#091;PRIMARY]\ngo<\/code><\/pre>\n\n\n\n<p>We notice that the clustered index is partitioned:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"836\" height=\"389\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-49.png\" alt=\"\" class=\"wp-image-39822\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-49.png 836w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-49-300x140.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-49-768x357.png 768w\" sizes=\"auto, (max-width: 836px) 100vw, 836px\" \/><\/figure>\n\n\n\n<p>However, the nonclustered index ix_transactions_label_date is not partitioned. Indeed, it was not created using the clause: ON SchemePartition_Transactions(transactions_date).<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"374\" height=\"136\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-50.png\" alt=\"\" class=\"wp-image-39823\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-50.png 374w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-50-300x109.png 300w\" sizes=\"auto, (max-width: 374px) 100vw, 374px\" \/><\/figure>\n\n\n\n<p>The table looks something like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"396\" height=\"361\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-51.png\" alt=\"\" class=\"wp-image-39824\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-51.png 396w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-51-300x273.png 300w\" sizes=\"auto, (max-width: 396px) 100vw, 396px\" \/><\/figure>\n\n\n\n<p>We also mentioned that this table was replicated:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"352\" height=\"76\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-52.png\" alt=\"\" class=\"wp-image-39826\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-52.png 352w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-52-300x65.png 300w\" sizes=\"auto, (max-width: 352px) 100vw, 352px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"438\" height=\"193\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-53.png\" alt=\"\" class=\"wp-image-39825\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-53.png 438w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-53-300x132.png 300w\" sizes=\"auto, (max-width: 438px) 100vw, 438px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"458\" height=\"891\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-54.png\" alt=\"\" class=\"wp-image-39827\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-54.png 458w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-54-154x300.png 154w\" sizes=\"auto, (max-width: 458px) 100vw, 458px\" \/><\/figure>\n\n\n\n<p>We now need to create our archive table, which will be an identical copy of the source table (transactions):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>create table transactions_archive\n(\n\ttransactions_id int identity(1,1) not null,\n\ttransactions_label nvarchar(50) not null,\n\ttransactions_amount int not null,\n\ttransactions_date date not null\n) on SchemePartition_Transactions(transactions_date)\n\nalter table transactions_archive add constraint pk_transactions_archive primary key clustered(transactions_id, transactions_date)\ngo\n\ncreate nonclustered index ix_transactions_label_date on transactions_archive(transactions_label) include (transactions_date) on SchemePartition_Transactions(transactions_date)\ngo\n<\/code><\/pre>\n\n\n\n<p>This table is partitioned, as are its indexes (clustered and nonclustered). However, this is not the case for the transactions table, and this will cause issues when executing ALTER SWITCH commands to perform partition switches.<\/p>\n\n\n\n<p>We then check what the partitioning of our tables looks like:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select \n\tOBJECT_NAME(p.object_id) as obj_name, \n\tf.name, \n\tp.partition_number, \n\tp.rows\n\t\tfrom sys.system_internals_allocation_units a\n\t\t\tjoin sys.partitions p\n\t\t\t\ton p.partition_id = a.container_id\n\t\t\tjoin sys.filegroups f on a.filegroup_id = f.data_space_id\n\t\n\twhere p.object_id in (object_id('transactions'), object_id('transactions_archive'))\n\torder by obj_name, p.index_id, p.partition_number\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"330\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-55.png\" alt=\"\" class=\"wp-image-39828\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-55.png 903w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-55-300x110.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-55-768x281.png 768w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/figure>\n\n\n\n<p>We now need to perform the following operations:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure that the nonclustered index ix_transactions_label_date on the transactions table is partitioned. To do this, it must be recreated. However, this table is replicated, and explicit index creation and deletion commands are not replicated. This is why we need to modify certain publication properties (allow_anonymous and immediate_sync), remove the article from the publication, partition the nonclustered index, and then add the article back to the publication.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"882\" height=\"85\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-57.png\" alt=\"\" class=\"wp-image-39831\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-57.png 882w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-57-300x29.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-57-768x74.png 768w\" sizes=\"auto, (max-width: 882px) 100vw, 882px\" \/><\/figure>\n\n\n\n<p>Reference: <a href=\"https:\/\/learn.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2012\/ms151870(v=sql.110)?redirectedfrom=MSDN\">https:\/\/learn.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2012\/ms151870(v=sql.110)?redirectedfrom=MSDN<\/a><\/p>\n\n\n\n<p>We have the following steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove the transactions article from the publication<\/li>\n\n\n\n<li>Modify the following properties: allow_anonymous and immediate_sync<\/li>\n\n\n\n<li>(Re)create and partition the nonclustered index<\/li>\n\n\n\n<li>Modify certain replication properties to allow SQL commands like ALTER SWITCH<\/li>\n\n\n\n<li>Add the transactions and transactions_article articles to the publication<\/li>\n<\/ul>\n\n\n\n<p>First, we remove the transactions article from the publication (note that in our client\u2019s case, several dozen tables were replicated).<\/p>\n\n\n\n<p>Second, we only want to generate a snapshot for the modified objects (transactions and transactions_archive).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'allow_anonymous', \n\t@value = 'false'\ngo\n\nexec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'immediate_sync', \n\t@value = 'false'\ngo<\/code><\/pre>\n\n\n\n<p>We (re)create the nonclustered index on the transactions table in order to partition it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>drop index ix_transactions_label_date on &#091;dbo].&#091;transactions]\ngo\n\ncreate nonclustered index ix_transactions_label_date on transactions(transactions_label) on SchemePartition_Transactions(transactions_date)\ngo<\/code><\/pre>\n\n\n\n<p>Our index is now partitioned:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"828\" height=\"297\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-59.png\" alt=\"\" class=\"wp-image-39833\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-59.png 828w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-59-300x108.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-59-768x275.png 768w\" sizes=\"auto, (max-width: 828px) 100vw, 828px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"505\" height=\"211\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-58.png\" alt=\"\" class=\"wp-image-39832\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-58.png 505w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-58-300x125.png 300w\" sizes=\"auto, (max-width: 505px) 100vw, 505px\" \/><\/figure>\n\n\n\n<p>We then need to modify certain publication properties to allow ALTER SWITCH commands:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_helppublication @publication = N'Publication_MyTransactions'\ngo\n\nexec sp_changepublication\n\t@publication = N'Publication_MyTransactions',\n\t@property = N'allow_partition_switch',\n\t@value = N'true'\ngo\n\nexec sp_changepublication\n\t@publication = N'Publication_MyTransactions',\n\t@property = N'replicate_partition_switch',\n\t@value = N'true'\ngo\n\nexec sp_helppublication @publication = N'Publication_MyTransactions'\ngo<\/code><\/pre>\n\n\n\n<p>The article can be added easily via (for example) the graphical interface:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"691\" height=\"270\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-60.png\" alt=\"\" class=\"wp-image-39834\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-60.png 691w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-60-300x117.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/figure>\n\n\n\n<p>We take partitioning into account in the following way:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"884\" height=\"387\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-61.png\" alt=\"\" class=\"wp-image-39835\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-61.png 884w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-61-300x131.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-61-768x336.png 768w\" sizes=\"auto, (max-width: 884px) 100vw, 884px\" \/><\/figure>\n\n\n\n<p>It is very important to modify the properties of only these two articles and not those of already published articles (if any). Modifying the properties of already published articles would invalidate the existing snapshot and force SQL Server to generate a snapshot of all articles.<\/p>\n\n\n\n<p>The above modification was made to the two articles (transactions and transactions_archive).<\/p>\n\n\n\n<p>Once these steps are completed, we need to generate a snapshot via the corresponding job:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"568\" height=\"199\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-62.png\" alt=\"\" class=\"wp-image-39836\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-62.png 568w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-62-300x105.png 300w\" sizes=\"auto, (max-width: 568px) 100vw, 568px\" \/><\/figure>\n\n\n\n<p>The corresponding objects are taken into account:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"619\" height=\"623\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-63.png\" alt=\"\" class=\"wp-image-39837\" style=\"width:619px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-63.png 619w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-63-298x300.png 298w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-63-150x150.png 150w\" sizes=\"auto, (max-width: 619px) 100vw, 619px\" \/><\/figure>\n\n\n\n<p>Once these operations are completed, we can see that our objects have been replicated and that the elements below have been taken into account:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Partitioned nonclustered index<\/li>\n\n\n\n<li>Partition scheme<\/li>\n\n\n\n<li>Partition function<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"845\" height=\"682\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-64.png\" alt=\"\" class=\"wp-image-39838\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-64.png 845w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-64-300x242.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-64-768x620.png 768w\" sizes=\"auto, (max-width: 845px) 100vw, 845px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-replicated-partition-switch\"><strong><strong>Replicated partition switch<\/strong><\/strong><\/h3>\n\n\n\n<p>We now want to perform partition switches and verify that these commands are replicated.<\/p>\n\n\n\n<p>Here is the corresponding code to run on the publisher (I am moving my data from partition 2 of my transactions table to partition 2 of my transactions_archive table):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>alter table transactions switch partition 2 to transactions_archive partition 2\n<\/code><\/pre>\n\n\n\n<p>Before (from the publisher):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"357\" height=\"407\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-65.png\" alt=\"\" class=\"wp-image-39840\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-65.png 357w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-65-263x300.png 263w\" sizes=\"auto, (max-width: 357px) 100vw, 357px\" \/><\/figure>\n\n\n\n<p>After (from the publisher):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"341\" height=\"405\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-66.png\" alt=\"\" class=\"wp-image-39842\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-66.png 341w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-66-253x300.png 253w\" sizes=\"auto, (max-width: 341px) 100vw, 341px\" \/><\/figure>\n\n\n\n<p>From the subscriber:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"323\" height=\"399\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-67.png\" alt=\"\" class=\"wp-image-39843\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-67.png 323w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-67-243x300.png 243w\" sizes=\"auto, (max-width: 323px) 100vw, 323px\" \/><\/figure>\n\n\n\n<p>Here is the related code :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>:connect TR-Pub-1\\INSTANCE2019\n\nuse MyTransactions\ngo\n\nalter table transactions switch partition 2 to transactions_archive partition 2\ngo\n\nselect \n\tOBJECT_NAME(p.object_id) as obj_name, \n\tf.name, \n\tp.partition_number, \n\tp.rows\n\t\tfrom sys.system_internals_allocation_units a\n\t\t\tjoin sys.partitions p\n\t\t\t\ton p.partition_id = a.container_id\n\t\t\tjoin sys.filegroups f on a.filegroup_id = f.data_space_id\n\t\n\twhere p.object_id in (object_id('transactions'), object_id('transactions_archive'))\n\torder by obj_name, p.index_id, p.partition_number\ngo\n\n:connect TR-Sub-1\\INSTANCE2019\n\nuse MyTransactions\ngo\n\nselect \n\tOBJECT_NAME(p.object_id) as obj_name, \n\tf.name, \n\tp.partition_number, \n\tp.rows\n\t\tfrom sys.system_internals_allocation_units a\n\t\t\tjoin sys.partitions p\n\t\t\t\ton p.partition_id = a.container_id\n\t\t\tjoin sys.filegroups f on a.filegroup_id = f.data_space_id\n\t\n\twhere p.object_id in (object_id('transactions'), object_id('transactions_archive'))\n\torder by obj_name, p.index_id, p.partition_number\ngo\n<\/code><\/pre>\n\n\n\n<p>We then need to roll back the modification of the following properties: allow_anonymous et immediate_sync.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'immediate_sync', \n\t@value = 'true'\ngo\n\nexec sp_changepublication \n\t@publication = N'Publication_MyTransactions', \n\t@property = N'allow_anonymous', \n\t@value = 'true'\ngo\n\nexec sp_helppublication @publication = N'Publication_MyTransactions'\ngo<\/code><\/pre>\n\n\n\n<p>Thank you. Amine Haloui.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The client\u2019s environment includes two SQL Server instances (I1 and I2). One of the databases, D1 (on I1), is replicated to I2 via transactional replication. Additionally, the tables in this database are partitioned. The largest table (which is the one of interest) is approximately 350 GB in size and contains around 800 million rows. This [&hellip;]<\/p>\n","protected":false},"author":147,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368,99],"tags":[366,301],"type_dbi":[],"class_list":["post-39799","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-sql-server","tag-partitioning","tag-replication"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Customer case study - Partition switching and transactional replication - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Customer case study - Partition switching and transactional replication\" \/>\n<meta property=\"og:description\" content=\"The client\u2019s environment includes two SQL Server instances (I1 and I2). One of the databases, D1 (on I1), is replicated to I2 via transactional replication. Additionally, the tables in this database are partitioned. The largest table (which is the one of interest) is approximately 350 GB in size and contains around 800 million rows. This [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-31T19:02:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-07T16:09:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\" \/>\n\t<meta property=\"og:image:width\" content=\"848\" \/>\n\t<meta property=\"og:image:height\" content=\"418\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Amine Haloui\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Amine Haloui\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\"},\"author\":{\"name\":\"Amine Haloui\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe\"},\"headline\":\"Customer case study &#8211; Partition switching and transactional replication\",\"datePublished\":\"2025-07-31T19:02:32+00:00\",\"dateModified\":\"2025-08-07T16:09:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\"},\"wordCount\":1343,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\",\"keywords\":[\"Partitioning\",\"Replication\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Development &amp; Performance\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\",\"name\":\"Customer case study - Partition switching and transactional replication - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\",\"datePublished\":\"2025-07-31T19:02:32+00:00\",\"dateModified\":\"2025-08-07T16:09:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png\",\"width\":848,\"height\":418},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Customer case study &#8211; Partition switching and transactional replication\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe\",\"name\":\"Amine Haloui\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"caption\":\"Amine Haloui\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/aminehaloui\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Customer case study - Partition switching and transactional replication - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/","og_locale":"en_US","og_type":"article","og_title":"Customer case study - Partition switching and transactional replication","og_description":"The client\u2019s environment includes two SQL Server instances (I1 and I2). One of the databases, D1 (on I1), is replicated to I2 via transactional replication. Additionally, the tables in this database are partitioned. The largest table (which is the one of interest) is approximately 350 GB in size and contains around 800 million rows. This [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/","og_site_name":"dbi Blog","article_published_time":"2025-07-31T19:02:32+00:00","article_modified_time":"2025-08-07T16:09:03+00:00","og_image":[{"width":848,"height":418,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png","type":"image\/png"}],"author":"Amine Haloui","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Amine Haloui","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/"},"author":{"name":"Amine Haloui","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"headline":"Customer case study &#8211; Partition switching and transactional replication","datePublished":"2025-07-31T19:02:32+00:00","dateModified":"2025-08-07T16:09:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/"},"wordCount":1343,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png","keywords":["Partitioning","Replication"],"articleSection":["Database Administration &amp; Monitoring","Database management","Development &amp; Performance","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/","url":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/","name":"Customer case study - Partition switching and transactional replication - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png","datePublished":"2025-07-31T19:02:32+00:00","dateModified":"2025-08-07T16:09:03+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-41.png","width":848,"height":418},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/customer-case-study-partition-switching-and-transactional-replication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Customer case study &#8211; Partition switching and transactional replication"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe","name":"Amine Haloui","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","caption":"Amine Haloui"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/aminehaloui\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/147"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=39799"}],"version-history":[{"count":31,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39799\/revisions"}],"predecessor-version":[{"id":40059,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39799\/revisions\/40059"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=39799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=39799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=39799"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=39799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}