{"id":5011,"date":"2015-06-23T18:25:00","date_gmt":"2015-06-23T16:25:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/"},"modified":"2015-06-23T18:25:00","modified_gmt":"2015-06-23T16:25:00","slug":"sql-server-2016-availability-groups-and-the-new-ssisdb-support","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/","title":{"rendered":"SQL Server 2016 : availability groups and the new SSISDB support"},"content":{"rendered":"<p>This blog post is focused on the new supportability of SSIDB catalog on AlwaysOn architecture.<\/p>\n<p>Others studies are available here:<\/p>\n<ul>\n<li><a href=\"\/sql-server-2016-availability-groups-and-the-new-option-dbfailover-\" target=\"_blank\" rel=\"noopener noreferrer\">New database issue level detection for automatic failover<\/a><\/li>\n<li><a href=\"\/sql-server-2016-native-support-for-json\" target=\"_blank\" rel=\"noopener noreferrer\">Potential features in standard edition<\/a><\/li>\n<li><a href=\"\/sql-server-2016-availability-groups-and-automatic-failover-enhancements\" target=\"_self\" rel=\"noopener noreferrer\">Automatic failover enhancements<\/a><\/li>\n<li><a href=\"\/sql-server-2016-availability-groups-and-load-balancing-features\" target=\"_self\" rel=\"noopener noreferrer\">New load balancing features with read-only replicas<\/a><\/li>\n<li><a href=\"\/sql-server-2016-availability-groups-and-the-new-clustered-columnstore-index-support\" target=\"_blank\" rel=\"noopener noreferrer\">New clustered columnstore index support<\/a><\/li>\n<\/ul>\n<p>A couple of weeks ago, I was involved in an SSIS infrastructure project with SQL Server 2014. As you know, the SSIS architecture has fundamentally changed since SQL Server 2012 and has lead to a new way of administrating it from the DBA perspective. This is also particularly true when we have to take into account an AlwaysOn architecture with the new SSISDB catalog since SQL Server 2014..<\/p>\n<p>Indeed, when you want to include the SSISDB catalog to an SQL Server 2014 availability group, you have to perform some extra steps that are required according to the Microsoft SSIS blog post <a href=\"http:\/\/blogs.msdn.com\/b\/mattm\/archive\/2012\/09\/19\/ssis-with-alwayson.aspx\">here<\/a>. The task consists in creating manually some SQL Server jobs to leverage a failover event that requires the re-encryption of the database master key by the service master key on the new primary replica. Likewise, you will have to deal with the SSIS Server Maintenance job that is not AlwaysOn aware by default. Thus, deploying the SSISDB catalog in an availability group\u2019s environment is not an easy task with SQL Server 2014 but let\u2019s take a look at the new support AlwaysOn support with SQL Server 2016.<\/p>\n<p>Fortunately, Microsoft has built on the experience gained from the previous version. Unlike SQL Server 2014, the next version will provide an easier way to deploy and manage the SSISDB catalog in an AlwaysOn infrastructure. By referring to the BOL here, you will notice that all the configuration stuff is done directly from the availability groups wizard without scripting any additional object. So, my goal in this blog post will consist in understanding the internal changes made by Microsoft in this area.<\/p>\n<p>First of all, let\u2019s take a look at some changes by trying to add an SSISDB catalog. At this point, you will be asked to provide the password of your database master key before to continue as follows:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\" alt=\"blog_54_-_1-_aag_ssidb_database_master_key\" width=\"623\" height=\"135\" \/><\/p>\n<p>Yes, the SSIDB catalog uses intensively the encryption to protect sensitive data from projects, packages, parameters and so on. At this point,\u00a0 you may notice a warning icon point. In fact, the wizard warns us about configuring the AlwaysOn support for SSISDB in a final step because it is required in order to leverage availability group failover events.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_2-_aag_ssidb_validation_step.jpg\" alt=\"blog_54_-_2-_aag_ssidb_validation_step\" width=\"625\" height=\"160\" \/><\/p>\n<p>To enable AlwaysOn support we need to go the <em>Integration Services Catalog<\/em> node and we must include the concerned replica(s) as shown below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_3-_aag_ssidb_services_integration_services_node.jpg\" alt=\"blog_54_-_3-_aag_ssidb_services_integration_services_node\" width=\"265\" height=\"119\" \/><\/p>\n<p>&#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_4-_aag_ssidb_services_integration_services_alwayson_support.jpg\" alt=\"blog_54_-_4-_aag_ssidb_services_integration_services_alwayson_support\" width=\"392\" height=\"132\" \/><\/p>\n<p>My configuration is now finished. In a second step, we will have a look at the SQL Server agent jobs. Indeed, during my test I suspected that a lot of stuff was done by SQL Server behind the scene and I was right. It added two additional jobs as shown below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_5-_aag_ssidb_catalog_jobs.jpg\" alt=\"blog_54_-_5-_aag_ssidb_catalog_jobs\" width=\"225\" height=\"75\" \/><\/p>\n<p>First of all, the <strong>SSIS Failover Monitor Job<\/strong> is designed to run on regular basis in order to detect failover events by refreshing the state of the concerned replica(s) and finally by starting accordingly the SSISDB catalog with the <em>SSISDB.catalog.startup<\/em> stored procedure. I remember in the past having implemented this kind of failover detection mechanism with the first AlwaysOn release in order to monitor availability group failover events (see my blog post <a href=\"http:\/\/blog.developpez.com\/mikedavem\/p12169\/sql-server-2012\/dtecter-un-basculement-automatique-avec-sql-server-2012-alwayson\" target=\"_self\" rel=\"noopener noreferrer\">here<\/a>).<\/p>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">DECLARE<span style=\"font-size: 9.5pt;font-family: Consolas\"> @role <span style=\"color: blue\">int<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">DECLARE<span style=\"font-size: 9.5pt;font-family: Consolas\"> @status <span style=\"color: blue\">tinyint<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">SET<span style=\"font-size: 9.5pt;font-family: Consolas\"> @role <span style=\"color: gray\">=<span style=\"color: gray\">(<span style=\"color: blue\">SELECT [role] <\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">FROM [sys]<span style=\"color: gray\">.[dm_hadr_availability_replica_states] hars <\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: gray\">INNER <span style=\"color: gray\">JOIN [sys]<span style=\"color: gray\">.[availability_databases_cluster] adc <\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">ON hars<span style=\"color: gray\">.[group_id] <span style=\"color: gray\">= adc<span style=\"color: gray\">.[group_id] <\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">WHERE hars<span style=\"color: gray\">.[is_local] <span style=\"color: gray\">= 1 <span style=\"color: gray\">AND adc<span style=\"color: gray\">.[database_name] <span style=\"color: gray\">=<span style=\"color: red\">&#8216;SSISDB&#8217;<span style=\"color: gray\">)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">IF<span style=\"font-size: 9.5pt;font-family: Consolas\"> @role <span style=\"color: gray\">= 1<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">BEGIN<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">EXEC [SSISDB]<span style=\"color: gray\">.[internal]<span style=\"color: gray\">.[refresh_replica_status]<\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"font-size: 9.5pt;font-family: Consolas\">@server_name <span style=\"color: gray\">= <span style=\"color: red\">N&#8217;SQL161&#8242;<span style=\"color: gray\">, <\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @status <span style=\"color: gray\">= <\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @status <span style=\"color: blue\">OUTPUT<\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">IF @status <span style=\"color: gray\">= 1<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">EXEC [SSISDB]<span style=\"color: gray\">.[catalog]<span style=\"color: gray\">.[startup]<\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">END<\/span><\/div>\n<p>Moreover, we may also notice some changes about the second job <strong>SSIS Server Maintenance Job<\/strong>. Indeed, this job is directly designed to support the AlwaysOn feature regardless the current SSISDB configuration (included or not in an availability group)<\/p>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">DECLARE<span style=\"font-size: 9.5pt;font-family: Consolas\"> @role <span style=\"color: blue\">int<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">SET<span style=\"font-size: 9.5pt;font-family: Consolas\"> @role <span style=\"color: gray\">=<span style=\"color: gray\">(<span style=\"color: blue\">SELECT [role] <span style=\"color: blue\">FROM [sys]<span style=\"color: gray\">.[dm_hadr_availability_replica_states] hars <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: gray\">INNER <span style=\"color: gray\">JOIN [sys]<span style=\"color: gray\">.[availability_databases_cluster] adc <\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">ON hars<span style=\"color: gray\">.[group_id] <span style=\"color: gray\">= adc<span style=\"color: gray\">.[group_id] <\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">WHERE hars<span style=\"color: gray\">.[is_local] <span style=\"color: gray\">= 1 <span style=\"color: gray\">AND adc<span style=\"color: gray\">.[database_name] <span style=\"color: gray\">=<span style=\"color: red\">&#8216;SSISDB&#8217;<span style=\"color: gray\">)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">IF<span style=\"font-size: 9.5pt;font-family: Consolas\"> <span style=\"color: fuchsia\">DB_ID<span style=\"color: gray\">(<span style=\"color: red\">&#8216;SSISDB&#8217;<span style=\"color: gray\">) <span style=\"color: gray\">IS <span style=\"color: gray\">NOT <span style=\"color: gray\">NULL <span style=\"color: gray\">AND<span style=\"color: gray\">(@role <span style=\"color: gray\">IS <span style=\"color: gray\">NULL <span style=\"color: gray\">OR @role <span style=\"color: gray\">= 1<span style=\"color: gray\">)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">EXEC [SSISDB]<span style=\"color: gray\">.[internal]<span style=\"color: gray\">.[cleanup_server_retention_window]<\/span><\/span><\/span><\/span><\/div>\n<p>Finally, just a quick search in the SSISDB database gives us a good overview of the new objects related to the AlwaysOn feature:<\/p>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">select<\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name <span style=\"color: blue\">as [object_name]<span style=\"color: gray\">,<\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: blue\">type_desc<\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">from<span style=\"font-size: 9.5pt;font-family: Consolas\"> <span style=\"color: green\">sys<span style=\"color: gray\">.<span style=\"color: green\">objects<\/span><\/span><\/span><\/span><\/span><\/div>\n<div style=\"margin-bottom: 0.0001pt;line-height: normal;background: #d9d9d9 none repeat scroll 0% 0%\"><span style=\"font-size: 9.5pt;font-family: Consolas;color: blue\">where<span style=\"font-size: 9.5pt;font-family: Consolas\"> name <span style=\"color: gray\">like <span style=\"color: red\">&#8216;%replica%&#8217;<\/span><\/span><\/span><\/span><\/div>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_6-_aag_ssidb_objects.jpg\" alt=\"blog_54_-_6-_aag_ssidb_objects\" width=\"319\" height=\"116\" \/><\/p>\n<ul>\n<li>The procedure <em>add_replica_info<\/em> seems to be used for adding a new SQL Server instance as replica in the SSISDB catalog.<\/li>\n<li>The <em>update_replica_info<\/em> procedure seems to be used for updating the replica state in the <em>alwayson_support_state<\/em> internal table.<\/li>\n<li>The <em>refresh_replica_status<\/em> seems to be used by the <em>SSIS Failover Monitor Job<\/em> on regular basis for updating the replica configuration state as well as detecting failover events.<\/li>\n<li>However, according to my tests, I didn\u2019t found neither any explicit call of the <em>delete_replica_info<\/em> stored procedure or guideline that explains how to exclude a AlwaysOn replica from the SSIDB catalog. I will update this blog post when I get the response.<\/li>\n<\/ul>\n<p>My feeling is that the new SSISDB support for AlwaysOn will be a pretty cool feature of the next version. This is not the most important improvement in this area for sure but it will help each DBA that wants to implement the SSISDB catalog in an AlwaysOn infrastructure by avoiding an additional burden required with SQL Server 2014. I\u2019m looking forward the definitive package soon!<\/p>\n<p>By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog post is focused on the new supportability of SSIDB catalog on AlwaysOn architecture. Others studies are available here: New database issue level detection for automatic failover Potential features in standard edition Automatic failover enhancements New load balancing features with read-only replicas New clustered columnstore index support A couple of weeks ago, I was [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":5012,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[199],"tags":[466,84,51,566,592],"type_dbi":[],"class_list":["post-5011","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hardware-storage","tag-alwayson","tag-high-availability","tag-sql-server","tag-sql-server-2016","tag-ssis"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016 : availability groups and the new SSISDB support - dbi Blog<\/title>\n<meta name=\"description\" content=\"SQL Server 2016 : availability groups and the new SSISDB support\" \/>\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\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016 : availability groups and the new SSISDB support\" \/>\n<meta property=\"og:description\" content=\"SQL Server 2016 : availability groups and the new SSISDB support\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-23T16:25:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"701\" \/>\n\t<meta property=\"og:image:height\" content=\"153\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 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\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server 2016 : availability groups and the new SSISDB support\",\"datePublished\":\"2015-06-23T16:25:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/\"},\"wordCount\":954,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\",\"keywords\":[\"AlwaysOn\",\"High availability\",\"SQL Server\",\"SQL Server 2016\",\"SSIS\"],\"articleSection\":[\"Hardware &amp; Storage\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/\",\"name\":\"SQL Server 2016 : availability groups and the new SSISDB support - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\",\"datePublished\":\"2015-06-23T16:25:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"SQL Server 2016 : availability groups and the new SSISDB support\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_54_-_1-_aag_ssidb_database_master_key.jpg\",\"width\":701,\"height\":153},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016 : availability groups and the new SSISDB support\"}]},{\"@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\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016 : availability groups and the new SSISDB support - dbi Blog","description":"SQL Server 2016 : availability groups and the new SSISDB support","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\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016 : availability groups and the new SSISDB support","og_description":"SQL Server 2016 : availability groups and the new SSISDB support","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/","og_site_name":"dbi Blog","article_published_time":"2015-06-23T16:25:00+00:00","og_image":[{"width":701,"height":153,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server 2016 : availability groups and the new SSISDB support","datePublished":"2015-06-23T16:25:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/"},"wordCount":954,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg","keywords":["AlwaysOn","High availability","SQL Server","SQL Server 2016","SSIS"],"articleSection":["Hardware &amp; Storage"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/","name":"SQL Server 2016 : availability groups and the new SSISDB support - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg","datePublished":"2015-06-23T16:25:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"SQL Server 2016 : availability groups and the new SSISDB support","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_54_-_1-_aag_ssidb_database_master_key.jpg","width":701,"height":153},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-and-the-new-ssisdb-support\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016 : availability groups and the new SSISDB support"}]},{"@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\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5011","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\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5011"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5011\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/5012"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5011"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}