{"id":10233,"date":"2017-06-05T18:19:20","date_gmt":"2017-06-05T16:19:20","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-high-availability-and-resumable-online-indexes\/"},"modified":"2017-06-05T18:19:20","modified_gmt":"2017-06-05T16:19:20","slug":"sql-server-2017-high-availability-and-resumable-online-indexes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-high-availability-and-resumable-online-indexes\/","title":{"rendered":"SQL Server 2017 high availability and Resumable Online Indexes"},"content":{"rendered":"

Ensuring data availability is an important part of the high availability design. SQL Server AlwaysOn features including SQL Server FCIs and availability groups address some aspects of the problem but we may also rely on the online operations features to maximize data availability. Indeed, for some high-critical workloads, offline operations during the maintenance phase are not permitted and may contribute to call into question the entire architecture.<\/p>\n

But have you ever faced an outage from your primary replica leading to a failover to the secondary replica during the database maintenance timeline? Yes, your architecture is designed to behave in this way for sure but let\u2019s say you were in the middle of a rebuild index operation concerning a big table and everything is rolled back now. In this case, you will probably have to wait the next windows maintenance time to initiate another rebuild operation, but from the\u00a0start \u2026<\/p>\n

Fortunately, the new Resumable Online Indexes<\/strong> feature from SQL Server 2017 will come to the rescue and seems promising to address such situation. Some limitations are still around and well-documented in the Microsoft documentation<\/a> but I guess Microsoft will work hard to remove them in the future.<\/p>\n

Let\u2019s play a little bit with the Resumable Online Indexes<\/em> on my AG environment that includes two replicas with cluster_type = NONE meaning we don\u2019t rely on an underlying orchestrator to handle automatic failover of the AG resources. In this context, it will be sufficient to demonstrate how Resumable Online Indexes work.<\/p>\n

\"blog<\/a><\/p>\n

My initial configuration is as follows:<\/span><\/p>\n

\"blog<\/a><\/p>\n

I used the AdventureWorks2016 database with a bigTransactionHistory<\/em> table generated from the Adam Machanic<\/a> script.<\/p>\n

select \n\to.name as table_name,\n\ti.name as index_name,\n\tp.index_id,\n\tau.type_desc,\n\tau.used_pages,\n\tp.rows\nfrom \n\tsys.allocation_units as au\njoin \n\tsys.partitions as p on au.container_id = p.hobt_id\njoin \n\tsys.objects as o on o.object_id = p.object_id\nleft join \n\tsys.indexes as i on i.object_id = p.object_id\n\t\t\t\t\t\tand i.index_id = p.index_id\nwhere p.object_id = object_id('bigTransactionHistory');<\/pre>\n
\u00a0<\/a><\/p>\n
\"blog<\/a><\/p>\n
Let\u2019s then initiate a rebuild of the pk_bigTransactionHistory<\/em> index by using the new RESUMABLE<\/strong> option as follows:<\/span><\/p>\n
ALTER INDEX pk_bigTransactionHistory \nON bigTransactionHistory \nREBUILD\nWITH (ONLINE = ON, RESUMABLE = ON);\nGO<\/pre>\n
 <\/p>\n
By using this option, I made the rebuild phase \u201cresumable\u201d and I\u2019m now able to perform additional actions like pausing or aborting the index rebuild operation. I may also use MAX_DURATION parameter to setup the duration (in minutes) of the operation before to pause it. We may easily identify resumable sessions which are running on the SQL Server instance by using the sys.dm_exec_requests<\/em> DMV and new is_resumable<\/em> column.<\/p>\n
\"blog<\/a><\/p>\n
Let\u2019s initiate a manual failover while the index is rebuilt by SQL Server on the primary replica. Then during the outage of the first primary replica, I faced the following error message:<\/p>\n
\"blog<\/a><\/p>\n
A typical error message regarding the situation \u2026 but the most interesting part comes now. If we go through the new sys.index_resumable_operations DMV, we may notice our rebuild index operation is paused.<\/span><\/p>\n
SELECT \n\tiro.last_max_dop_used,\n\tiro.sql_text,\n\tiro.start_time,\n\tiro.last_pause_time,\n\tiro.total_execution_time,\n\tiro.page_count, \n\tiro.percent_complete\nFROM  \n\tsys.index_resumable_operations as iro\nJOIN\n\tsys.objects AS o ON o.object_id = iro.object_id<\/pre>\n
 <\/p>\n
\"blog<\/a><\/p>\n
From the above output, we may extract interesting column values:<\/p>\n