{"id":669,"date":"2024-07-26T08:00:00","date_gmt":"2024-07-26T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=669"},"modified":"2024-07-26T09:36:04","modified_gmt":"2024-07-26T14:36:04","slug":"creating-sql-server-ags-on-linux-part-3-configuring-pacemaker-resources-and-constraints","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=669","title":{"rendered":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints"},"content":{"rendered":"

Introduction<\/h2>\n

Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.<\/p>\n

Prerequisites<\/h3>\n

Before we begin, ensure that you have completed the steps from Parts 1<\/a> and 2<\/a>. You should have three Ubuntu 20.04 hosts with SQL Server installed, Pacemaker configured and running on all nodes, and an availability group created and operational.<\/p>\n

Step 1: Install SQL Server HA Tools<\/h3>\n

On all nodes, install the SQL Server High Availability tools, which are necessary for integrating SQL Server with Pacemaker:<\/p>\n

sudo apt -y update\nsudo apt -y install mssql-server-ha<\/code><\/pre>\n
Step 2: Create Pacemaker Resource for Availability Group<\/h3>\n
On the primary node (vm0), we will create the Pacemaker resource for the availability group:<\/p>\n
1. Create a file with Pacemaker login credentials:<\/p>\n
This step sets up a file containing the credentials that Pacemaker will use to authenticate with SQL Server.<\/p>\n
echo 'pacemakerLogin' >> ~\/pacemaker-passwd\necho 'Password123' >> ~\/pacemaker-passwd\nsudo mv ~\/pacemaker-passwd \/var\/opt\/mssql\/secrets\/passwd\nsudo chown root:root \/var\/opt\/mssql\/secrets\/passwd\nsudo chmod 400 \/var\/opt\/mssql\/secrets\/passwd<\/code><\/pre>\n
2. Determine the Virtual IP Address<\/strong><\/p>\n
The virtual IP address is used by clients to connect to the availability group, ensuring high availability and seamless failover. Choose an IP address that is not in use on your network and assign it to the availability group. This IP should be within the same subnet as your nodes and configured in your DNS settings.<\/p>\n
3. Create the Pacemaker resource for the availability group:<\/p>\n
These commands create the necessary Pacemaker resources to manage the SQL Server availability group. The first command creates the availability group resource, and the second command sets up a virtual IP address resource that will be used by the clients to connect to the primary replica. The constraints ensure the virtual IP is always associated with the primary replica.<\/p>\n
sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=60s promotable notify=true\nsudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.160.108.15\nsudo pcs constraint colocation add virtualip with master ag_cluster-clone INFINITY with-rsc-role=Master\nsudo pcs constraint order promote ag_cluster-clone then start virtualip<\/code><\/pre>\n
Replace 10.160.108.15<\/code> with the virtual IP address you have chosen.<\/p>\n
Step 3: Verify Pacemaker Resource Status<\/h3>\n
Check the status of the Pacemaker resources to ensure everything is set up correctly. This command will show the current state of all resources and their locations:<\/p>\n
sudo pcs resource status<\/code><\/pre>\n
Step 4: Manage Cluster Services<\/h3>\n
Make sure the required services are online and enabled to start at boot on all nodes. This ensures that the cluster services start automatically after a reboot:<\/p>\n
sudo systemctl enable corosync\nsudo systemctl enable pacemaker\nsudo systemctl enable pcsd\n\nsystemctl status corosync\nsystemctl status pacemaker\nsystemctl status pcsd<\/code><\/pre>\n
If any services are not running, start them. This command starts the cluster services if they are not already running:<\/p>\n
sudo systemctl start corosync\nsudo systemctl start pacemaker\nsudo systemctl start pcsd<\/code><\/pre>\n
Step 5: Handling Failovers<\/h3>\n
To manually move the availability group to another node, use these commands. This can be useful for testing or maintenance purposes:<\/p>\n
sudo pcs resource move ag_cluster-clone vm1 --master\nsudo pcs resource clear ag_cluster<\/code><\/pre>\n
To move the virtual IP address, use these commands. This ensures the virtual IP moves to the node that is currently the primary replica:<\/p>\n
sudo pcs resource move virtualip vm1\nsudo pcs resource clear virtualip<\/code><\/pre>\n
Step 6: Inspect Cluster Logs<\/h3>\n
For more detailed information on errors or warnings, check the Pacemaker logs. This command filters the logs to show only entries related to Pacemaker:<\/p>\n
cat \/var\/log\/syslog | grep pacemaker<\/code><\/pre>\n
If you suspect the Cluster Information Base (CIB) is out-of-date or corrupted on a node, synchronize it. This command synchronizes the CIB on the local node with the rest of the cluster:<\/p>\n
sudo pcs cluster sync<\/code><\/pre>\n
To restart the cluster services, be cautious of potential downtime. These commands stop and start the cluster services on all nodes, which can help resolve communication issues but may cause downtime:<\/p>\n
sudo pcs cluster stop --all\nsudo pcs cluster start --all<\/code><\/pre>\n
Conclusion<\/h3>\n
In this post, we configured Pacemaker resources and constraints to manage our SQL Server availability group effectively. These steps ensure that your SQL Server instances are highly available and can fail over seamlessly between nodes. In the next post, we will dive into advanced troubleshooting and performance tuning to optimize your high availability setup. Stay tuned for more detailed guidance as we continue our journey toward robust SQL Server high availability on Linux.<\/p>\n","protected":false},"excerpt":{"rendered":"
Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[36,20,21],"tags":[38,210,42,19,187,131,28,188],"class_list":["post-669","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-sql-server-on-linux","category-tutorial","tag-availability-groups","tag-failover-cluster","tag-high-availability","tag-linux","tag-pacemaker","tag-sql-server","tag-sql-server-on-linux","tag-ubuntu"],"yoast_head":"\nCreating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.\" \/>\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.sqltabletalk.com\/?p=669\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=669\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-07-26T13:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-07-26T14:36:04+00:00\" \/>\n<meta name=\"author\" content=\"Stephen Planck\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Stephen Planck\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669\"},\"author\":{\"name\":\"Stephen Planck\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"headline\":\"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints\",\"datePublished\":\"2024-07-26T13:00:00+00:00\",\"dateModified\":\"2024-07-26T14:36:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669\"},\"wordCount\":611,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"availability groups\",\"Failover Cluster\",\"High Availability\",\"Linux\",\"Pacemaker\",\"SQL Server\",\"SQL Server on Linux\",\"Ubuntu\"],\"articleSection\":[\"Availability Groups\",\"SQL Server on Linux\",\"Tutorial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=669#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=669\",\"name\":\"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-07-26T13:00:00+00:00\",\"dateModified\":\"2024-07-26T14:36:04+00:00\",\"description\":\"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=669\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=669#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"],\"url\":\"https:\/\/www.sqltabletalk.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk","description":"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.","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.sqltabletalk.com\/?p=669","og_locale":"en_US","og_type":"article","og_title":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk","og_description":"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.","og_url":"https:\/\/www.sqltabletalk.com\/?p=669","og_site_name":"SQL Table Talk","article_published_time":"2024-07-26T13:00:00+00:00","article_modified_time":"2024-07-26T14:36:04+00:00","author":"Stephen Planck","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Stephen Planck","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=669#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=669"},"author":{"name":"Stephen Planck","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"headline":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints","datePublished":"2024-07-26T13:00:00+00:00","dateModified":"2024-07-26T14:36:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=669"},"wordCount":611,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["availability groups","Failover Cluster","High Availability","Linux","Pacemaker","SQL Server","SQL Server on Linux","Ubuntu"],"articleSection":["Availability Groups","SQL Server on Linux","Tutorial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=669#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=669","url":"https:\/\/www.sqltabletalk.com\/?p=669","name":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-07-26T13:00:00+00:00","dateModified":"2024-07-26T14:36:04+00:00","description":"Welcome to the third part of our series on setting up SQL Server availability groups on Linux. In the previous post, we focused on enabling High Availability Disaster Recovery (HADR) on SQL Server and setting up the availability group. This post will guide you through configuring Pacemaker resources and constraints to manage the availability group effectively.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=669#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=669"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=669#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Creating SQL Server AGs on Linux: Part 3 \u2013 Pacemaker Resources and Constraints"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"],"url":"https:\/\/www.sqltabletalk.com\/?author=1"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/669","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=669"}],"version-history":[{"count":3,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/669\/revisions"}],"predecessor-version":[{"id":676,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/669\/revisions\/676"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}