{"id":34512,"date":"2024-08-19T10:59:45","date_gmt":"2024-08-19T08:59:45","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=34512"},"modified":"2024-08-19T17:48:02","modified_gmt":"2024-08-19T15:48:02","slug":"sql-server-basic-ag-one-listener-multiple-databases","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/","title":{"rendered":"SQL Server: Basic AG &#8211; One listener, multiple databases"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"h-introduction\">Introduction<\/h2>\n\n\n\n<p>When it is not possible to have SQL Server Enterprise and there is a need to set up an AlwaysOn cluster, it is possible to use SQL Server Standard and implement basic availability groups.<\/p>\n\n\n\n<p>However, the Standard edition of SQL Server comes with certain limitations when it comes to availability groups.<\/p>\n\n\n\n<p><strong>Here are some of these limitations<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Limited to 2 replicas<\/li>\n\n\n\n<li>No read-only access on the secondary replica<\/li>\n\n\n\n<li>No possibility to perform backups on the secondary<\/li>\n\n\n\n<li>1 database per availability group<\/li>\n<\/ul>\n\n\n\n<p><strong>However some clients come with the following constraints<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Install an application that deploys and installs multiple databases<\/li>\n\n\n\n<li>Use SQL Server Standard<\/li>\n\n\n\n<li>Use only one listener, as the applications will only use one listener to connect to the primary replica<\/li>\n<\/ul>\n\n\n\n<p><strong>Requirements<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1 database = 1 availability group = 0 or 1 listener<\/li>\n\n\n\n<li>We need to install multiple databases<\/li>\n\n\n\n<li>Our applications are configured to use only one listener (in their connection string), such as SharePoint, EasyVista, Business Central, M3&#8230;<\/li>\n\n\n\n<li>Therefore we will need to create multiple availability groups in order to benefit from high availability<\/li>\n<\/ul>\n\n\n\n<p><strong>Here are the questions that arise<\/strong><\/p>\n\n\n\n<p>If I need to install 10 databases for my application, this requires creating 10 availability groups and therefore 0 or several listeners.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>How can I ensure that all databases are in high availability with only one listener ?<\/li>\n\n\n\n<li>How can I have only one listener for our applications, multiple availability groups, and one database per availability group ?<\/li>\n\n\n\n<li>How can this limitation be overcome ?<\/li>\n<\/ul>\n\n\n\n<p><strong>Here is a summary of the solution<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We create our cluster<\/li>\n\n\n\n<li>We enable the AlwaysOn feature on all replicas<\/li>\n\n\n\n<li>We create our availability groups\n<ul class=\"wp-block-list\">\n<li>1 availability group per database. Each of these availability groups will not contain any listener.<\/li>\n\n\n\n<li>1 availability group containing a &#8220;monitoring&#8221; database and a listener\n<ul class=\"wp-block-list\">\n<li>When this availability group fails over, all other availability groups fail over as well. This is the &#8220;reference&#8221; database. All availability groups must share the same primary replica as the availability group that contains the &#8220;monitoring&#8221; database<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-use-cases\">Use cases<\/h2>\n\n\n\n<p><strong>Case 1<\/strong><\/p>\n\n\n\n<p>The availability groups share the same primary replica :<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"359\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.png\" alt=\"\" class=\"wp-image-34515\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-300x105.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-768x269.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image.png 1241w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>Case 2<\/strong><\/p>\n\n\n\n<p>The availability groups do not share the same primary replica :<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"370\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1-1024x370.png\" alt=\"\" class=\"wp-image-34516\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1-1024x370.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1-300x109.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1-768x278.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1.png 1241w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>Here are some details about the environment<\/strong><\/p>\n\n\n\n<p>Virtual machines :<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>Hostname<\/strong><\/td><td><strong>IP<\/strong><\/td><td><strong>Domain<\/strong><\/td><td><strong>Function<\/strong><\/td><td><strong>OS<\/strong><\/td><\/tr><tr><td>ad<\/td><td>192.168.2.10<\/td><td>lab.local<\/td><td>Domain controler<\/td><td>Windows Server 2022<\/td><\/tr><tr><td>sql-1<\/td><td>192.168.2.11<\/td><td>lab.local<\/td><td>Replica 1<\/td><td>Windows Server 2022<\/td><\/tr><tr><td>sql-2<\/td><td>192.168.2.12<\/td><td>lab.local<\/td><td>Replica 2<\/td><td>Windows Server 2022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Failover cluster details<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Failover cluster name : clu-1.lab.local<\/li>\n\n\n\n<li>IP : 192.168.2.13\/24<\/li>\n<\/ul>\n\n\n\n<p><strong>SQL Server details<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>Replica<\/strong><\/td><td><strong>IP<\/strong><\/td><td><strong>SQL Server version<\/strong><\/td><\/tr><tr><td>sql-1<\/td><td>192.168.2.11\/24<\/td><td>SQL Server 2022 Standard<\/td><\/tr><tr><td>sql-2<\/td><td>192.168.2.12\/24<\/td><td>SQL Server 2022 Standard<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>AlwaysOn details<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>AG name<\/strong><\/td><td><strong>Endpoint port<\/strong><\/td><td><strong>Port<\/strong><\/td><td><strong>Listener<\/strong><\/td><td><strong>IP<\/strong><\/td><\/tr><tr><td>ag-monitoring<\/td><td>5022<\/td><td>1433<\/td><td>lst-1<\/td><td>192.168.2.14\/24<\/td><\/tr><tr><td>ag-t1<\/td><td>5022<\/td><td>N\/A<\/td><td>N\/A<\/td><td>N\/A<\/td><\/tr><tr><td>ag-t2<\/td><td>5022<\/td><td>N\/A<\/td><td>N\/A<\/td><td>N\/A<\/td><\/tr><tr><td>ag-t3<\/td><td>5022<\/td><td>N\/A<\/td><td>N\/A<\/td><td>N\/A<\/td><\/tr><tr><td>ag-t4<\/td><td>5022<\/td><td>N\/A<\/td><td>N\/A<\/td><td>N\/A<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-global-architecture\">Global architecture<\/h2>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"547\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-2.png\" alt=\"\" class=\"wp-image-34517\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-2.png 865w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-2-300x190.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-2-768x486.png 768w\" sizes=\"auto, (max-width: 865px) 100vw, 865px\" \/><\/figure>\n\n\n\n<p><strong>Our SQL Server instances<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"878\" height=\"152\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-3.png\" alt=\"\" class=\"wp-image-34518\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-3.png 878w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-3-300x52.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-3-768x133.png 768w\" sizes=\"auto, (max-width: 878px) 100vw, 878px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"296\" height=\"164\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-4.png\" alt=\"\" class=\"wp-image-34519\" \/><\/figure>\n\n\n\n<p><strong>We integrate our databases to the availability groups and we check their states<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n\tdbs.name as dbs_name,\n\tdhdrs.synchronization_state_desc,\n\tar.replica_server_name\n\tFROM sys.dm_hadr_database_replica_states AS dhdrs\n\t\tINNER JOIN sys.availability_replicas AS ar\n\t\t\tON dhdrs.replica_id = ar.replica_id\n\t\tINNER JOIN sys.databases AS dbs\n\t\t\tON dhdrs.database_id = dbs.database_id\nORDER BY ar.replica_server_name ASC<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"328\" height=\"210\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-5.png\" alt=\"\" class=\"wp-image-34520\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-5.png 328w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-5-300x192.png 300w\" sizes=\"auto, (max-width: 328px) 100vw, 328px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"310\" height=\"168\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-6.png\" alt=\"\" class=\"wp-image-34521\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-6.png 310w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-6-300x163.png 300w\" sizes=\"auto, (max-width: 310px) 100vw, 310px\" \/><\/figure>\n\n\n\n<p><strong>We check the availability groups configuration<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n\tar.replica_server_name,\n\tar.availability_mode_desc,\n\tag.&#091;name] AS dbs_name,\n\tdhars.role_desc,\n\tCASE\n\t\tWHEN ag.basic_features = 1 THEN 'Basic AG'\n\t\tELSE 'Not Basic AG'\n\tEND AS ag_type\n\tFROM sys.availability_replicas AS ar\n\t\tINNER JOIN sys.availability_groups AS ag\n\t\t\tON ar.group_id = ag.group_id\n\t\tINNER JOIN sys.dm_hadr_availability_replica_states AS dhars\nON ar.replica_id = dhars.replica_id<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"512\" height=\"214\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-7.png\" alt=\"\" class=\"wp-image-34522\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-7.png 512w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-7-300x125.png 300w\" sizes=\"auto, (max-width: 512px) 100vw, 512px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-solutions\">Solutions<\/h2>\n\n\n\n<p>We present two solutions to ensure that the availability groups share the same primary replica:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Solution 1:<\/strong> a job that runs every 5 minutes and checks if the availability groups share the same replica. If they do not, a failover is performed. The &#8220;reference&#8221; availability group in our case is the group named &#8220;ag-monitoring&#8221; The other availability groups must share the same primary replica.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Solution 2:<\/strong> an alert and a job that perform the following tasks:\n<ul class=\"wp-block-list\">\n<li>The alert is triggered when a failover is detected. The alert then calls a job.\n<ul class=\"wp-block-list\">\n<li>This job is divided into 3 steps:\n<ul class=\"wp-block-list\">\n<li>Save the failover date<\/li>\n\n\n\n<li>Wait for a certain duration before checking the state of the availability groups (the failover may take some time)<\/li>\n\n\n\n<li>Check the configuration of the availability groups (what their primary replica is)\n<ul class=\"wp-block-list\">\n<li>If the availability groups ag-t1, ag-t2, ag-t3, and ag-t4 do not share the same primary replica, a failover is performed<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-implementation\">Implementation<\/h2>\n\n\n\n<p><strong>Solution 1<\/strong><\/p>\n\n\n\n<p>The job is deployed on both replicas. We check if the database is open for read\/write before performing any operations.<\/p>\n\n\n\n<p>Job configuration :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"466\" height=\"98\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-8.png\" alt=\"\" class=\"wp-image-34523\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-8.png 466w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-8-300x63.png 300w\" sizes=\"auto, (max-width: 466px) 100vw, 466px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"568\" height=\"148\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-9.png\" alt=\"\" class=\"wp-image-34524\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-9.png 568w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-9-300x78.png 300w\" sizes=\"auto, (max-width: 568px) 100vw, 568px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"824\" height=\"174\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-10.png\" alt=\"\" class=\"wp-image-34525\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-10.png 824w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-10-300x63.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-10-768x162.png 768w\" sizes=\"auto, (max-width: 824px) 100vw, 824px\" \/><\/figure>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF ((SELECT DATABASEPROPERTYEX('monitoring', 'Updateability')) = 'READ_WRITE')\nBEGIN\nDECLARE @replica_server NVARCHAR(100),\n\t\t@ag_name NVARCHAR(100),\n\t\t@failover_command NVARCHAR(100)\n\nDECLARE cursor_failover_commands CURSOR FOR\n   WITH T_AG_Monitoring (replica_server, ag_name, role_name)\nAS\n(\nSELECT\n\tar.replica_server_name,\n\tag.&#091;name] as ag_name,\n\tdhars.role_desc\n\n\tFROM sys.availability_replicas AS ar\n\t\tINNER JOIN sys.availability_groups AS ag\n\t\t\tON ar.group_id = ag.group_id\n\t\tLEFT JOIN sys.dm_hadr_availability_replica_states as dhars\n\t\t\tON ar.replica_id = dhars.replica_id\n\t\n\tWHERE ag.&#091;name] = 'ag-monitoring'\n),\nT_AG_Databases (replica_server, ag_name, role_name)\nAS\n(\n\tSELECT\n\tar.replica_server_name,\n\tag.&#091;name] as ag_name,\n\tdhars.role_desc\n\n\tFROM sys.availability_replicas AS ar\n\t\tINNER JOIN sys.availability_groups AS ag\n\t\t\tON ar.group_id = ag.group_id\n\t\tLEFT JOIN sys.dm_hadr_availability_replica_states as dhars\n\t\t\tON ar.replica_id = dhars.replica_id\n\t\n\tWHERE ag.&#091;name] &lt;&gt; 'ag-monitoring'\n) SELECT\n\tT_AG_Databases.replica_server,\n\tT_AG_Databases.ag_name,\n\tCASE\n\t\tWHEN T_AG_Databases.role_name IS NULL THEN 'ALTER AVAILABILITY GROUP &#091;' + T_AG_Databases.ag_name + '] FAILOVER;'\n\tEND AS failover_command\n\tFROM T_AG_Monitoring\n\t\tRIGHT JOIN T_AG_Databases\n\t\t\tON T_AG_Monitoring.replica_server = T_AG_Databases.replica_server \n\t\t\tAND T_AG_Monitoring.role_name = T_AG_Databases.role_name\n\nOPEN cursor_failover_commands\nFETCH cursor_failover_commands INTO @replica_server, @ag_name, @failover_command\n \nWHILE @@FETCH_STATUS = 0\nBEGIN\n\tIF (LEN(@failover_command) &gt;= 1)\n\tBEGIN\n\t\tEXEC(@failover_command);\t\n\tEND\n    FETCH cursor_failover_commands INTO @replica_server, @ag_name, @failover_command\nEND\n \nCLOSE cursor_failover_commands\nDEALLOCATE cursor_failover_commands\nEND<\/code><\/pre>\n\n\n\n<p><strong>Solution 2<\/strong><\/p>\n\n\n\n<p>The alert and the job are present on both replicas.<\/p>\n\n\n\n<p>Alert configuration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"690\" height=\"308\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-11.png\" alt=\"\" class=\"wp-image-34526\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-11.png 690w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-11-300x134.png 300w\" sizes=\"auto, (max-width: 690px) 100vw, 690px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"686\" height=\"276\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-12.png\" alt=\"\" class=\"wp-image-34527\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-12.png 686w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-12-300x121.png 300w\" sizes=\"auto, (max-width: 686px) 100vw, 686px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"528\" height=\"306\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-13.png\" alt=\"\" class=\"wp-image-34528\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-13.png 528w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-13-300x174.png 300w\" sizes=\"auto, (max-width: 528px) 100vw, 528px\" \/><\/figure>\n\n\n\n<p>Job configuration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"444\" height=\"100\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-14.png\" alt=\"\" class=\"wp-image-34529\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-14.png 444w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-14-300x68.png 300w\" sizes=\"auto, (max-width: 444px) 100vw, 444px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"434\" height=\"194\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-15.png\" alt=\"\" class=\"wp-image-34530\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-15.png 434w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-15-300x134.png 300w\" sizes=\"auto, (max-width: 434px) 100vw, 434px\" \/><\/figure>\n\n\n\n<p>Code :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE\nGO\n\nBEGIN TRANSACTION\nDECLARE @ReturnCode INT\nSELECT @ReturnCode = 0\n\nIF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'&#091;Uncategorized (Local)]' AND category_class=1)\n\nBEGIN\nEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'&#091;Uncategorized (Local)]'\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\nEND\n\nDECLARE @jobId BINARY(16)\n\nEXEC @ReturnCode =&nbsp; msdb.dbo.sp_add_job @job_name=N'ag_monitoring_s2',\n\n@enabled=0,\n\n@notify_level_eventlog=0,\n@notify_level_email=0,\n@notify_level_netsend=0,\n@notify_level_page=0,\n@delete_level=0,\n@description=N'No description available.',\n@category_name=N'&#091;Uncategorized (Local)]',\n\n@owner_login_name=N'sa', @job_id = @jobId OUTPUT\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ag_monitoring',\n@step_id=1,\n@cmdexec_success_code=0,\n@on_success_action=3,\n@on_success_step_id=0,\n@on_fail_action=2,\n@on_fail_step_id=0,\n@retry_attempts=1,\n@retry_interval=1,\n@os_run_priority=0, @subsystem=N'TSQL',\n\n@command=N'IF ((SELECT DATABASEPROPERTYEX(''monitoring'', ''Updateability'')) = ''READ_WRITE'')\n\nBEGIN\n\nMERGE monitoring.dbo.t_tracefailover_tfr AS t_target\nUSING (\nSELECT\nid,\nlast_occurrence_date,\nlast_occurrence_time\nFROM msdb.dbo.sysalerts\n\n) AS t_source\n\nON t_source.id = t_target.tfr_id\n\nWHEN NOT MATCHED THEN\n\nINSERT (tfr_id, tfr_last_occurence_date, tfr_last_occurence_time) VALUES (t_source.id, t_source.last_occurrence_date, t_source.last_occurrence_time)\n\nWHEN MATCHED THEN\n\nUPDATE SET t_target.tfr_last_occurence_date = t_source.last_occurrence_date, t_target.tfr_last_occurence_time = t_source.last_occurrence_time;\n\nEND\nELSE\n\nBEGIN\nPRINT ''Secondary replica'';\nEND',\n\n@database_name=N'master',\n\n@flags=0\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ag_wait_before_check',\n\n@step_id=2,\n\n@cmdexec_success_code=0,\n@on_success_action=3,\n@on_success_step_id=0,\n@on_fail_action=2,\n@on_fail_step_id=0,\n@retry_attempts=0,\n@retry_interval=0,\n@os_run_priority=0, @subsystem=N'TSQL',\n\n@command=N'BEGIN&nbsp;\n&nbsp;&nbsp;&nbsp; WAITFOR DELAY ''00:01'';&nbsp;\nEND;',\n\n@database_name=N'master',\n@flags=0\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ag_check_state',\n\n@step_id=3,\n@cmdexec_success_code=0,\n@on_success_action=1,\n@on_success_step_id=0,\n@on_fail_action=2,\n@on_fail_step_id=0,\n@retry_attempts=0,\n@retry_interval=0,\n@os_run_priority=0, @subsystem=N'TSQL',\n\n@command=N'IF ((SELECT DATABASEPROPERTYEX(''monitoring'', ''Updateability'')) = ''READ_WRITE'')\n\nBEGIN\n\nDECLARE @replica_server NVARCHAR(100),\n@ag_name NVARCHAR(100),\n@failover_command NVARCHAR(100)\n\nDECLARE cursor_failover_commands CURSOR FOR\n&nbsp;&nbsp; WITH T_AG_Monitoring (replica_server, ag_name, role_name)\nAS\n\n(\nSELECT\nar.replica_server_name,\nag.&#091;name] as ag_name,\ndhars.role_desc\nFROM sys.availability_replicas AS ar\nINNER JOIN sys.availability_groups AS ag\nON ar.group_id = ag.group_id\nLEFT JOIN sys.dm_hadr_availability_replica_states as dhars\nON ar.replica_id = dhars.replica_id\nWHERE ag.&#091;name] = ''ag-monitoring''\n),\n\nT_AG_Databases (replica_server, ag_name, role_name)\n\nAS\n(\n\nSELECT\nar.replica_server_name,\nag.&#091;name] as ag_name,\ndhars.role_desc\nFROM sys.availability_replicas AS ar\nINNER JOIN sys.availability_groups AS ag\nON ar.group_id = ag.group_id\nLEFT JOIN sys.dm_hadr_availability_replica_states as dhars\nON ar.replica_id = dhars.replica_id\nWHERE ag.&#091;name] &lt;&gt; ''ag-monitoring''\n\n) SELECT\nT_AG_Databases.replica_server,\nT_AG_Databases.ag_name,\nCASE\nWHEN T_AG_Databases.role_name IS NULL THEN ''ALTER AVAILABILITY GROUP &#091;'' + T_AG_Databases.ag_name + ''] FAILOVER;''\nEND AS failover_command\n\nFROM T_AG_Monitoring\n\nRIGHT JOIN T_AG_Databases\nON T_AG_Monitoring.replica_server = T_AG_Databases.replica_server\nAND T_AG_Monitoring.role_name = T_AG_Databases.role_name\n\nOPEN cursor_failover_commands\nFETCH cursor_failover_commands INTO @replica_server, @ag_name, @failover_command\nWHILE @@FETCH_STATUS = 0\n\nBEGIN\n\nIF (LEN(@failover_command) &gt;= 1)\n\nBEGIN\nPRINT ''Failover'';\nEXEC(@failover_command);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\n\nEND\n&nbsp;&nbsp;&nbsp; FETCH cursor_failover_commands INTO @replica_server, @ag_name, @failover_command\nEND\n\nCLOSE cursor_failover_commands\nDEALLOCATE cursor_failover_commands\nEND',\n\n@database_name=N'master',\n\n@flags=0\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'\n\nIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback\n\nCOMMIT TRANSACTION\n\nGOTO EndSave\n\nQuitWithRollback:\n\n&nbsp;&nbsp;&nbsp; IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION\n\nEndSave:\n\nGO<\/code><\/pre>\n\n\n\n<p><strong>First solution test :<\/strong><\/p>\n\n\n\n<p>Initial configuration : the availability groups share the same primary replica.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"322\" height=\"432\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-16.png\" alt=\"\" class=\"wp-image-34532\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-16.png 322w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-16-224x300.png 224w\" sizes=\"auto, (max-width: 322px) 100vw, 322px\" \/><\/figure>\n\n\n\n<p>We perform a failover of the availability group ag-t1.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"412\" height=\"174\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-17.png\" alt=\"\" class=\"wp-image-34533\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-17.png 412w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-17-300x127.png 300w\" sizes=\"auto, (max-width: 412px) 100vw, 412px\" \/><\/figure>\n\n\n\n<p>We check the configuration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"326\" height=\"350\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-18.png\" alt=\"\" class=\"wp-image-34534\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-18.png 326w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-18-279x300.png 279w\" sizes=\"auto, (max-width: 326px) 100vw, 326px\" \/><\/figure>\n\n\n\n<p>The job is executing:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"376\" height=\"236\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-19.png\" alt=\"\" class=\"wp-image-34535\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-19.png 376w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-19-300x188.png 300w\" sizes=\"auto, (max-width: 376px) 100vw, 376px\" \/><\/figure>\n\n\n\n<p>We check the configuration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"320\" height=\"346\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-20.png\" alt=\"\" class=\"wp-image-34536\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-20.png 320w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-20-277x300.png 277w\" sizes=\"auto, (max-width: 320px) 100vw, 320px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Solution 1<\/strong> has 2 drawbacks:\n<ul class=\"wp-block-list\">\n<li>The job could potentially be triggered during a failover<\/li>\n\n\n\n<li>The job runs every 5 minutes. Therefore, in the worst case, there could be a 5-minute delay in aligning the availability groups<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Solution 2<\/strong> is more complex and has the following drawbacks:\n<ul class=\"wp-block-list\">\n<li>The current backup history is a copy of the dbo.sysalerts table. As a result, the data in this table does not use the GETDATE() function. We could add a column to store the date of the last failover<\/li>\n\n\n\n<li>The job can be executed multiple times because it is triggered by an alert (alt_failover)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>It is also important to verify data synchronization before performing a failover.<\/p>\n\n\n\n<p>Thank you, Amine Haloui.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction When it is not possible to have SQL Server Enterprise and there is a need to set up an AlwaysOn cluster, it is possible to use SQL Server Standard and implement basic availability groups. However, the Standard edition of SQL Server comes with certain limitations when it comes to availability groups. Here are some [&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":[466,921,51],"type_dbi":[],"class_list":["post-34512","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-sql-server","tag-alwayson","tag-basic-availability-groups","tag-sql-server"],"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: Basic AG - One listener, multiple databases - 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\/sql-server-basic-ag-one-listener-multiple-databases\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server: Basic AG - One listener, multiple databases\" \/>\n<meta property=\"og:description\" content=\"Introduction When it is not possible to have SQL Server Enterprise and there is a need to set up an AlwaysOn cluster, it is possible to use SQL Server Standard and implement basic availability groups. However, the Standard edition of SQL Server comes with certain limitations when it comes to availability groups. Here are some [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-08-19T08:59:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-08-19T15:48:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.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=\"8 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-basic-ag-one-listener-multiple-databases\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/\"},\"author\":{\"name\":\"Amine Haloui\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/221331d69d49c63fca67069b49b813fe\"},\"headline\":\"SQL Server: Basic AG &#8211; One listener, multiple databases\",\"datePublished\":\"2024-08-19T08:59:45+00:00\",\"dateModified\":\"2024-08-19T15:48:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/\"},\"wordCount\":810,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/08\\\/image-1024x359.png\",\"keywords\":[\"AlwaysOn\",\"Basic availability groups\",\"SQL Server\"],\"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\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/\",\"name\":\"SQL Server: Basic AG - One listener, multiple databases - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/08\\\/image-1024x359.png\",\"datePublished\":\"2024-08-19T08:59:45+00:00\",\"dateModified\":\"2024-08-19T15:48:02+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/221331d69d49c63fca67069b49b813fe\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/08\\\/image.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/08\\\/image.png\",\"width\":1241,\"height\":435},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-basic-ag-one-listener-multiple-databases\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Basic AG &#8211; One listener, multiple databases\"}]},{\"@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":"SQL Server: Basic AG - One listener, multiple databases - 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\/sql-server-basic-ag-one-listener-multiple-databases\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Basic AG - One listener, multiple databases","og_description":"Introduction When it is not possible to have SQL Server Enterprise and there is a need to set up an AlwaysOn cluster, it is possible to use SQL Server Standard and implement basic availability groups. However, the Standard edition of SQL Server comes with certain limitations when it comes to availability groups. Here are some [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/","og_site_name":"dbi Blog","article_published_time":"2024-08-19T08:59:45+00:00","article_modified_time":"2024-08-19T15:48:02+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.png","type":"","width":"","height":""}],"author":"Amine Haloui","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Amine Haloui","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/"},"author":{"name":"Amine Haloui","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"headline":"SQL Server: Basic AG &#8211; One listener, multiple databases","datePublished":"2024-08-19T08:59:45+00:00","dateModified":"2024-08-19T15:48:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/"},"wordCount":810,"commentCount":1,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.png","keywords":["AlwaysOn","Basic availability groups","SQL Server"],"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\/sql-server-basic-ag-one-listener-multiple-databases\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/","name":"SQL Server: Basic AG - One listener, multiple databases - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image-1024x359.png","datePublished":"2024-08-19T08:59:45+00:00","dateModified":"2024-08-19T15:48:02+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/08\/image.png","width":1241,"height":435},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-basic-ag-one-listener-multiple-databases\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Basic AG &#8211; One listener, multiple databases"}]},{"@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\/34512","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=34512"}],"version-history":[{"count":22,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/34512\/revisions"}],"predecessor-version":[{"id":34559,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/34512\/revisions\/34559"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=34512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=34512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=34512"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=34512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}