Search Results for: availability groups

What is an availability group listener – An analogy

13th October 2020 By John McCormack Leave a Comment

This post is part of the October 2020 edition of #tsql2sday. Thanks to Rob Volk for hosting (blog|twitter) and for coming up with a great topic. “Data Analogies, or: Explain Databases Like I’m Five!” I genuinely can’t wait to read some of the responses. I think it’s going to be a very educational series of posts. I’ve chosen to explain how an availability group listener works by using an analogy.

What is an availability group listener – An Analogy

Let’s say you run a business and have multiple phone numbers. You may have an office number, a mobile number and a fancy 0800 number to give your business the impression of being much bigger, like your national competitors. You put the 0800 number on your van, website, all of your advertising and your business cards. You’re not actually a national scale business though and you don’t have a receptionist to handle the calls into your 0800 number. So what happens to those calls?

They are routed through to your preferred number, usually your office number, but you can change it to your mobile number when you are out of office. You could even set a preference for it to try to route the call to your office first, then try your mobile phone if the office is unavailable. Customers that have your mobile number or office number can still call you directly on those but they will not be rerouted if either of those phones are unavailable. If you change your mobile number, you can just update the routing to use your new number and the customer is unaware of the change.

What is an availability group listener – A slightly (but not much) deeper dive

What’s in a name

Like the analogy above, the AG listener uses an address that can route SQL Server connections to a primary or secondary replica within an availability group. It includes a DNS name which is unique within a domain, an IP address or several, and a listener port designation.

Connections to SQL Server where availability groups are in use should use the listener name instead of the server name. This means that if any replica in the AG is unavailable, connections will just be routed to the available replicas, meaning no loss of service to the client. You can utilise your SQL Server resources more evenly if using a listener because there is an option to direct certain connections to a readable secondary replica. You can also offset backups to secondary replicas. All of this spreads the load more evenly across available replicas. If you just route everything through the primary, it can be overworked whilst your secondary replicas are doing next to nothing. The secondary replicas still need to be fully licensed so it is a shame, and a waste of money for them to sit by idly.

Port

If you’re looking for simplicity, you should designate the listener port as 1433, as no specific port declaration is required in the connection string. However, if you need to, you can designate a different port, but must include it in your connection string.

Seemless

If your primary replica becomes unavailable and you have automatic failover enabled, it means that your old secondary replica can switch seemlessly to becoming the new primary. No new connections will notice a thing.

Spread the load

If you have more than one secondary, you can spread the load across a set of readable secondaries. This means that all replicas are taking a share of the workload. Prior to SQL Server 2016, only one preferred replica would receive all of the of the read intent traffic.

Thanks again to Rob for coming up with an innovative topic.

John

If you liked this post, why not read:

https://johnmccormack.it/2019/04/test-read-intent-connections-to-an-ag-listener/

Alert if your availability group fails over

10th July 2019 By John McCormack Leave a Comment

A simple way to send an alert if your Always On Availability Group fails over. You can run the t-sql below or set it up visually in SQL Agent.

USE [msdb]

/*
Sends an alert if the AG group fails over.
I've limited the requests to one alert per group (by specifying the DB name in @event_description_keyword
Let me know what you think of this approach, I found it better than multiple alerts for each DB in the Availability Group
*/

EXEC msdb.dbo.sp_add_alert @name=N'AG role change - AG1',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@database_name=N'',
@event_description_keyword=N'PRD_DB1',
@notification_message=N'There has been a failover of your Always On Availability Group AG1 - Please investigate.',
@job_id=N'00000000-0000-0000-0000-000000000000'

-- Add an operator if it doesn't already exist
EXEC msdb.dbo.sp_add_operator @name=N'John McCormack',
@enabled=1,
@pager_days=0,
@email_address=N'john.mccormack@example.com'

-- Add a notification
EXEC msdb.dbo.sp_add_notification @alert_name=N'AG role change - AG1', @operator_name=N'John McCormack', @notification_method = 1

Advanced configurations using RDS Parameter Groups

28th July 2020 By John McCormack Leave a Comment

Advanced configurations using RDS Parameter Groups

In this video, we take a look at how to set advanced configurations using RDS parameter groups. Due to permissions restrictions, some parts of the usual DBA toolkit are disabled such as setting trace flags or amending the sp_configure settings. With parameter groups, you can still make these changes where necessary.

Course homepage | Next video

How do I run SQL Server on AWS?

6th November 2018 By John McCormack 3 Comments

How do I run SQL Server on AWS?

Running SQL Server on AWS can be done in 2 ways.

Relation Database Service (RDS): AWS’s managed solution where some of the administration (maintenance, backups and patching) is handled for you.
EC2: Your very own virtual machine in the cloud. With EC2, you manage SQL Server, just like you would do on-premises. This gives you full control over your SQL instance.

Why not just use Azure?

Azure may be the right choice for you. This purpose of this post is to show that you do have options and that Azure is not the only choice. Really, you should weigh up what you need from your cloud provider and make a choice. Managing a multi-cloud environment can be challenging. If you are working for a company already heavily invested it AWS, it may make more sense to stay with AWS. Even if not, AWS’s SQL Server offering is mature and RDS’s features are improving all the time so you should consider what you can achieve with AWS.

Differences/Similarities/Pros and Cons

Feature	EC2	RDS
Use SSMS	✔	✔
Use database as source or target for SSIS	✔	✔
Full control over the instance	✔
Automated backups		✔
Automated patching		✔
Always On Availability groups	✔	✔
AWS-managed Multi AZ deployment		✔
Log Shipping	✔
Replication	✔
Database mail	✔
Linked Servers	✔

A full list of included and excluded features can be viewed at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

RDS key facts

Limited control
- The master user name you use when you create a DB instance is a SQL Server Authentication login that is a member of the processadmin, public, and setupadmin fixed server roles. It is not a member of the sysadmin fixed server role.
- Anyone who creates a db is assigned to db_owner role on that DB.
More administration tasks are handled automatically e.g. backups, OS patching and SQL patching
Bring Your Own licence is no longer available on RDS
License included (covers multi AZ)
- Standard
- Web (Must only be used for public and internet-accessible webpages, websites, web applications, and web services.)
- Enterprise
Backups
- Can still be pushed to S3
- Native backup and restore using .bak files to/from S3
- Versions: 2008 R2 – 2017
Limited to 30 databases per instance
Window/Mixed authentication
- http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerWinAuth.html
Multi AZ is very simple to set up
TDE at rest
SSL for data in transit
No database Mail

EC2 approach

Full control of instance
Bring your own licence (BYOL) options
Licence included (AMI)
- Standard
- Enterprise
High Availability Disaster Recovery HADR
- You can use Always On Availability Groups
  - Automatic failover between AZs
  - Synchronous commit
  - Latency about 1ms to 2 ms
- Manual failover between Regions option for increase resiliency

Backups

EC2, you are responsible for all of your own backups. Backups can be pushed to S3 to save reduce costs. I have a full post dedicated to SQL Server backups on EC2. You could also store backups on EBS volumes attached to your instance however this is much more expensive than storing on S3.

RDS will handle backups for you. You can restore to a point in time in RDS withing the backup window you configure. Transaction logs are taken every 5 minutes so you may experience data loss up to 5 minutes. All databases are restored with 1 second of each other. The downside to this is that transactions spanning multiple databases may not be recovered consistently.

Summary

Features are being added to RDS all the time however if you need features in the excluded list, you would need to go with an EC2 installation. However if your business size doesn’t allow for DBAs or database specialists, RDS may be the best option as AWS take care of all the installation and maintenance for you.

Resources

T-SQL Tuesday #143 Wrap Up

19th October 2021 By John McCormack 2 Comments

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

https://johnmccormack.it/2021/10/t-sql-tuesday-143-short-code-examples/

I learned so much by hosting this and made sure I gave due care to reading every post. It was also a lot of fun and allowed me to interact with people in the community that I haven’t met before. If you haven’t hosted T-SQL Tuesday before, please contact Steve Jones as we are always looking for new hosts.

Wrap Up

Rob Farley – Short and to the point like I asked for, Rob details a quick way to find objects. And he was happy to clear up for the reader that I didn’t mean GOTO as in the old BASIC syntax you could run on your commodore 64. (For me it was an Amstrad CPC464)
http://blogs.lobsterpot.com.au/2021/10/12/go-to-scripts/

Koen Verbeek – Koen shows us numbers tables, tally tables and a dates table. These are really useful constructs for allowing your queries to go “set based”. Essential reading for anyone who cares about performance. https://sqlkover.com/t-sql-tuesday-143-short-code-examples/

Aaron Bertrand – Aaron shows us how he “bulletproofs” his answers for dba.stackexchange and Stack Overflow. db<>fiddle was new to me. I love some of Aaron’s demo database names like [master (Restoring…)]. I ran the create command on my test instance and had to drop the DB right away as it was giving me the chills.
https://sqlblog.org/2021/10/12/t-sql-tuesday-143-worst-metadata

Deborah Melkin – Deborah shows us a really useful debugging trick when creating stored procedures that use dynamic sql. Many of us have been lost in dynamic sql at some point, and this snippet is great at helping you see where you are.
https://debthedba.wordpress.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Kenneth Fisher – Kenneth shares a compendium of previous posts which all require some serious reading. My favourite was “all jobs that ran during a given time frame”
https://sqlstudies.com/2021/10/12/code-examples-t-sql-tuesday-143/

Jeff Hill – Jeff shared 4 great PowerShell snippets. True to this month’s request, they are short and incredibly useful. Want to know what version of Windows you are running on your Server or when it was last rebooted, look no further.
https://sqladm.in/posts/tsql-tuesday-143/

Chad Baldwin – Chad is a newcomer to T-SQL Tuesday and chipped in with a stellar first post. I must admit, I’ve never given much thought to how to format a result set as I’ll usually do it in the client, but when you need to; it is possible as Chad shows. But that’s only the start. There’s too much to discuss in this digest as he also covers tally tables, random numbers and overcoming the divide by 0 problem. Did I mention he also covers docker, monitoring/filtering log files and setting aliases. Cap doffed.
https://chadbaldwin.net/2021/10/12/tsql-tuesday-short-code.html

Andy Yun – Random numbers, random delays (I wonder if Scotrail use this script) and random strings. Thanks Andy for a great post. There are great scripts on their own and for building into more complex ones.
https://sqlbek.wordpress.com/2021/10/12/t-sql-tuesday-143-random-fun/

Kevin Chant – Kevin discusses just how to get the most out Glenn Berry’s diagnostic scripts, specifically in relation to missing indexes. He also shows a create table syntax and highlights how effective it has been for him in his training sessions around Dev Ops.
https://www.kevinrchant.com/2021/10/12/t-sql-tuesday-143-two-of-my-personal-go-to-scripts/

Andy Mallon – Andy stores all of his useful scripts in a DBA database. It’s a popular approach and I was hoping someone would mention this. Andy goes beyond this though and has converted a lot of scripts into Stored Procedures. Whilst having a local scripts folder is great; if you can put your code into a stored procedure in a database which you deploy to all the servers you manage, there is no need to panic and find the scripts when the pressure is on. I must admit I love this approach and I’ll be downloading Andy’s database to look further into it.
https://am2.co/2021/10/t-sql-tuesday-143-my-favorite-short-scripts/

Jason Brimhall – Jason talks about all things endpoints here and I found the code examples so handy. I’ve already used them. Not only can you use them to validate your endpoints, but you can also use them to fix some issues as well. On a personal note, just what I needed.
http s ://bit.ly/3lAOMmF

Tom Zika – Tom shares loads of useful snippets including regex and t-sql. Wow – one regex snippet shows us how to find table variable declarations and turn them into temp tables. You could make a killing selling this one trick to consultants. Tom also shares a mega handy way to check permissions using impersonation as well as a great method to find referencing objects.
https://straightforwardsql.com/posts/short-code-examples/

Mikey Bronowski – Mikey shows us a handy way to execute multiple queries including dynamic ones and also tells us about agent_datetime() function. I have to admit I’ve never used that function but it looks so useful for when you are interrogating those msdb agent job tables. I will definitely be adding it my list. Finally, he shares a useful query for pulling back table data with his added enhancement (however a nice little plug for DBATools hints he now has a better way of approaching this).
https://bronowski.it/t-sql-tuesday-143-short-code-examples/

Todd Kleinhans – Todd focuses on Python and he is the only person to do so. I won’t give away his one liner but it’s just the sort of thing I was looking for. Are you feeling Zen?
https://toddkleinhans.wordpress.com/2021/10/12/t-sql-tuesday-143-import-this/

Mala Mahadevan – Mala shares some top class queries for interrogating query store. Query store has so much useful data that knowing how to get started querying it will be a big win for some.
https://curiousaboutdata.com/2021/10/12/tsql-tuesday-143-short-code-examples/

Chad Callihan – Chad mentioned 3 handy t-sql snippets and then shared a gem for keeping Brent Ozar’s First Responder Kit up to date. (Hint he uses DBATools). DBATools and FRK are amongst the most essential free tools for any DBA and beyond. If you run anything like sp_blitz or sp_blitzcache, it’s worth keeping it up to date and this method shows how to do it in only a few lines of code.
https://callihandata.com/2021/10/12/t-sql-tuesday-143-handy-short-scripts/

Deepthi Goguri – Deepthi shares some of the best of the rest by highlighting some of her favourite community scripts. From help with migrations to troubleshooting replication, it just goes to show that there’s no need to reinvent the wheel when there’s a perfectly good script out there that meets your needs.
https://dbanuggets.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Steve Jones – Did you know that you could get a tally table with just 4 key strokes? Steve shows you how, leveraging on the power of SQL Prompt by Redgate. This is taking snippets to a new level.
https://voiceofthedba.com/2021/10/13/t-sql-tuesday-143-short-code/

Jess Pomfret – Aloha to Jess who squeezes her entry in on time due to the Hawaiian time loophole. Want to find out if certain accounts are local admins on remote servers? Jess shares a quick and efficient method for finding this out. Being Jess, of course she is using PowerShell to make her life easier. I for one will be stealing this.
https://jesspomfret.com/t-sql-tuesday-143/

Eitan Blumin – Eitan takes the opportunity to link to some of his past blog posts which are full of useful code however he doesn’t stop there. With a new entry for T-SQL Tuesday, Eitan shows us how to move database files to a new location in Always On Availability Groups without breaking HADR. Ok at 374 lines, it’s a bit more than a snippet but it’s really great code so we’ll let that one slide.
https://eitanblumin.com/2021/10/13/t-sql-tuesday-143-powershell-move-db-files-alwayson-availability-groups/

Shane O’Neill – Shane also mentions agent_datetime(). It’s a cool function for converting the very user unfriendly ms format that we see in msdb tables. Shane points out it might not be the most efficient function however when you don’t have much data to bring back, it’s much quicker than rewriting the thing. Shane being Shane (Big Powershell fan) also points out a few great PowerShell commands for formatting and sorting and shows how they can be used in conjunction with other commands that yield really useful results.
https://nocolumnname.blog/2021/10/12/t-sql-tuesday-143-short-code-examples/

P.S. I’ve taken every bit of care to check my comments and on twitter but if I have missed your post, please let me know and I’ll include it immediately.