John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Search Results for: availability groups

What is an availability group listener – An analogy

13th October 2020 By John McCormack Leave a Comment

T-SQL Tuesday logo - What is an availability group listenerThis 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/

Filed Under: front-page, T-SQL Tuesday Tagged With: ag, AG Listener, always on availability group, availability group, listener, sql, SQL server, t-sql tuesday

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

 

Filed Under: Always On Availability Groups, front-page

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

Filed Under: AWS RDS, AWS SQL Server, SQL Server, Training

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

  • Microsoft SQL Server on Amazon RDS
  • Importing and Exporting SQL Server Databases
  • Restoring a DB Instance to a Specified Time
  • EC2 SQL Server Backups to Amazon S3

Filed Under: AWS RDS, front-page

SQL Server on Amazon RDS (Free Course)

23rd July 2020 By John McCormack Leave a Comment

SQL Server on Amazon RDS Course

I’ve put together a short video series on running SQL Server on Amazon RDS. It covers some basics and can be completed in around an hour, with several very short videos. It’s intended for SQL professionals who are just getting started with AWS, but might also be enjoyed by AWS users, looking to get started with a relational database management system.

It’s my first attempt at putting together a training plan. So any feedback will be gratefully received. Either in the blog comments or YouTube video comments.

Contents

1. Introduction to SQL Server on Amazon RDS (Scroll down)
2. Benefits and limitations of AWS RDS
3. Create your first RDS instance using the AWS console
4. Connecting to your AWS RDS instance
5. Advanced Configurations using Parameter Groups
6. Securing your SQL Server AWS RDS Instance
7. Automating Deployments using PowerShell
8. Backup and restore for AWS RDS
9. Providing High-Availability through Multiple Availability Zones
10. Monitoring Your Instances using CloudWatch

Introduction to SQL Server on Amazon RDS

Download slides

Next Video

Filed Under: AWS RDS, AWS SQL Server, front-page, Training

  • 1
  • 2
  • 3
  • 4
  • Next Page »
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2021