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

John McCormack's blogs

You can run SQL Server on AWS?

18th June 2019 By John McCormack 1 Comment


Barbecue food at DataGrillenMy presentation

I presented on running SQL Server on AWS at DataGrillen in Germany. Specifically, I covered the differences between RDS and EC2, migration options and the AWS CLI. Despite being a daunting prospect, I enjoyed speaking at a major conference for the first time.

I received a few questions and managed to answer most. For the one I could not remember, I promised to provide an answer.

I was asked who provides the SSL certificates when using SSL connections with your RDS instance. The answer:  When you create a SQL Server DB instance, Amazon RDS creates an SSL certificate for it. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. Read more.

Link to slides, please feel free to view or download.

The event

DataGrillen is a community Data Platform event which is free to attend and draws speakers and attendees from across the world. It is so inclusive and welcoming, it’s easy to see why it’s a can’t miss event for so many many people.

Speakers dinner DataGrillen 2019
Speakers dinner
DataGrillen 2019 welcomes new speakers
Welcoming new speakers
DataGrillen 2019 lunch
Lunch
Chris Taylor talks containers
Chris Taylor talks Azure Devops
John McCormack and Chrissy LeMaire
With Chrissy LeMaire
DataGrillen 2019 beer drinking
Beers and networking
BArbecue food at Data Grillen
Barbecue food
DataGrillen 2019 after party
Some post Grillen drinks

Links to my other AWS content

  • https://johnmccormack.it/2017/12/ec2-sql-server-backups-to-amazon-s3/
  • https://johnmccormack.it/2018/08/how-to-create-a-table-in-aws-athena/

AWS documentation

  • https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
  • https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html
  • https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html
  • https://docs.aws.amazon.com/cli/latest/userguide/install-windows.html#install-msi-on-windows 
  • https://aws.amazon.com/blogs/database/powering-up-database-mail-on-amazon-rds-for-sql-server-how-under-armour-runs-database-mail-on-amazon-rds-for-sql-server/ 

Thanks

John

Filed Under: AWS RDS, AWS SQL Server Tagged With: aws, DataGrillen2019, ec2, rds, SQL server

Distributed Replay Error: Failed to set proper database for the connection

3rd May 2019 By John McCormack Leave a Comment

Distributed Replay Error: Failed to set proper database for the connection – Troubleshooting dreplay.exe

If you see Failed to set proper database for the connection in your replayresult.trc, it is worth looking at the row below it (in profiler).

Distributed Replay Error: Failed to set proper database for the connection

Check if the TextData for the row below (with the same ReplaySequence) would run from the database listed in the DatabaseName column.

Hint, either its a database that you haven’t restored to your test environment or you have named the databases differently to production. e.g. you have prefixed the names with perf_

 

Related posts

  • https://johnmccormack.it/2016/10/error-dreplay-could-not-find-any-resources-appropriate-for-the-specified-culture-or-the-neutral-culture/

Useful links

  • https://docs.microsoft.com/en-us/sql/tools/distributed-replay/sql-server-distributed-replay
  • https://www.sqlservercentral.com/blogs/unusual-errors-with-distributed-replay

Filed Under: Distributed Replay Tagged With: Distributed replay, dreplay

Test read intent connections to an AG Listener

19th April 2019 By John McCormack 4 Comments

To test read intent connections to an AG Listener, I prefer to use SQLCMD but you can also test easily using SSMS.

SQLCMD

The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

Read/Write Connections
-sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters -E -q "SELECT @@SERVERNAME;"

Read only Connections
-sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters-E -q "SELECT @@SERVERNAME;" -Kreadonly

The instance that you are connected to will show in the command prompt. Type exit to leave sqlcmd.

SSMS

In object explorer, click Connect and choose Database Engine.

Then, in the bottom right hand side of the dialog box, click on Options >>

In Connection Properties, Click Connect to database and then <Browse server ..>. Choose a DB that is in your availability group.

Connect To Server SSMS

Then click on Additional Connection Parameters and type in ApplicationIntent=ReadOnly

ApplicationIntent=ReadOnly SSMS

Click connect and run SELECT @@SERVERNAMEand you should expect to see the instance name of your secondary replica, providing you have set up the read only routing correctly.

If you change connection and remove ApplicationIntent=ReadOnly from the Additional Connection Parameters, you should see the result as the name of your primary instance.

Summary

Hopefully these 2 simple techniques to test read intent connections to an AG Listener will be useful and help save you time. It’s a simple blog post but I wanted to write it because I was looking at the problem in too much depth and missing the obvious mistake of choosing my database context.

Popular posts

  • https://johnmccormack.it/2019/03/put-tempdb-files-on-d-drive-in-azure-iaas/
  • https://johnmccormack.it/2017/12/ec2-sql-server-backups-to-amazon-s3/
  • https://johnmccormack.it/2018/10/glasgow-super-meetup-aws-athena-presentation/

Filed Under: Always On Availability Groups, front-page Tagged With: AG Listener, Always On, Availability Groups, SQL server

Where the extended events eventfile is located

5th April 2019 By John McCormack Leave a Comment

Do you need to find out where the extended events eventfile is located? Simply run the query below and use the WHERE clause if you know the name of the XE session.

[sql]

SELECT
s.name,
CAST(st.target_data AS XML).value(‘(/EventFileTarget/File/@name)[1]’,’nvarchar(256)’) AS target_data_file,
CAST(st.target_data AS XML) AS target_data_xml
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON st.event_session_address = s.[address]
WHERE s.name = ‘Custom XE Session Name’ — Optional if you know the name

[/sql]

More info about the DMVs

  • dm_xe_sessions
  • sys.dm_xe_session_targets

Filed Under: Extended Events, SQL Server

Put tempdb files on D drive in Azure IAAS

21st March 2019 By John McCormack 5 Comments

Data loss warning Azure VMTempdb files on D drive you say?

Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.

TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.

Just look at these results! Even for someone like me who sometimes looks at charts and often can’t see the obvious, I don’t think there is any denying when the change took place here.

tempdb latency azure IAAS
Blazing fast now

 

Overview of how to configure SQL Server to use D drive for tempdb on Azure VM

  1. Run the ALTER DATABASE command for all of the files you need to change e.g.
    • ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'D:\TempDB\tempdev.mdf')
  2. Create Folder D:\TempDB or something similar
  3. Restart SQL Server and make sure your files are created in D: as expected
  4. Manually remove any old tempdb files still lying around

Now to make sure tempdb is created when the VM restarts

  1. Create a Powershell script which will create the D:\TempDB folder on startup and start the SQL services
    • You could use this script at https://community.idera.com/database-tools/blog/b/community_blog/posts/configuring-tempdb-on-azure-iaas-for-sql-server 
    • Schedule this script with a Task Scheduler startup job
    • Change your execution policy to remote signed if it isn’t that. Set-ExecutionPolicy RemoteSigned
  2. Change the startup type of your SQL Services to Automatic (Delayed Start)
  3. Test this thoroughly in dev before you rely on it in production. If it doesn’t work, you’ll need to remember how you configured tempdb and manually create the folders, then start SQL Server. Not a fun task under pressure.
More tempdb goodness

Configuring TempDB for SQL Server 2016 instances

IT Certification Category (English)728x90

Filed Under: Azure, Azure VM, front-page Tagged With: azure, azure iaas, sql server on Azure, tempdb

  • « Previous Page
  • 1
  • …
  • 12
  • 13
  • 14
  • 15
  • 16
  • …
  • 21
  • 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 © 2023