It has been a while since I wrote a blog post for TSQL Tuesday and there is no better time then now following on from Brent Ozar’s Invite about our favourite data types in SQL server.
SQL server BIGINT
2
Category Archives: T-SQL Tuesday
T-SQL Tuesday Sharing Cookies
It has been a while since I have participated in a T-SQL Tuesday but I felt the urge to do so this month because of the host – Shane O’Neill (a good friend) and the topic of cookies (not the edible type).
T-SQL Tuesday #98 Round Up
Thank you to everyone that took the time to write and contribute, I enjoyed reading about how you conquered your challenges, here is a round-up in no particular order.
T-SQL Tuesday #98 – Your Technical Challenges Conquered
Welcome to the January 2018 edition of T-SQL Tuesday and I am your host BlobEater (Arun Sirpal).
If you do not know what T-SQL Tuesday is then a quick recap. T-SQL Tuesday is a monthly blog initiative hosted by a different blogger each month. This was founded by Adam Machanic (blog|@AdamMachanic) and is a great way of encouraging people to write. With that being said let’s move on to the topic which will be a challenging one.
Folks Who Have Made a Difference
TSQL Tuesday time hosted by Ewald (https://sqlonice.com/tsql-tuesday-96-folks-who-have-made-a-difference/) and quite simply one man has morphed me into who I am today – Paul Randal. Over two years ago I was a one of many that had the chance to be mentored by him. It lasted just under one year and the effects were huge for me.
How/Why/What is DevOps?
Thanks to Grant for hosting this month’s T-SQL Tuesday found (http://www.scarydba.com/2017/06/06/t-sql-tuesday-091-databases-devops/) where it is a chance to share our DevOps stories. I am not going into specifics of databases but I am going to write about it more generally.
Microsoft Data Platform is changing – so am I
This month’s T-SQL Tuesday entry hosted by Koen Verbeeck (http://sqlkover.com/t-sql-tuesday-89-invitation-the-times-they-are-a-changing/), a blog post about how we feel about the ever changing times within our technology space. Personally I love this new era of cloud computing and do not feel threatened in anyway.
Extreme situations require extreme commands
The title is adapted from a child-hood movie of mine and is my daily (database-related) WTF moment and it is my entry for this month’s T-SQL Tuesday found: http://www.pontop.dk/single-post/2017/03/07/Announcing-T-SQL-Tuesday-88-%E2%80%93-The-daily-database-related-WTF
SQL Server Bugs & Enhancement Requests
T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests hosted by Brent Ozar.
I did not want to miss out on this one because I want to mention a connect item that I logged last year which is still active.
This is an enhancement request for SQL Server where I got the idea from SQL database (Azure).
Wait Stats is my “go-to” thing, when you want to dig into performance issues everyone knows you will probably end up using sys.dm_os_wait_stats. You cannot use this in the Azure world, you have to use a DMV that is scoped to the database level. I think this would be a nice idea to have with the “earth” based SQL Servers – the ability to return information about all the waits encountered by threads that executed at the database level.
The connect item can be found at this link: https://connect.microsoft.com/SQLServer/feedback/details/2647332/sys-dm-db-wait-stats-dmv?tduid=(262281c4c73a682498780643b77e80d1)(256380)(2459594)(TnL5HPStwNw-KjxCoz0pGWobbq7q1MQTIw)()
An example of using the DMV that I would like:
WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_db_wait_stats ) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 99
It currently has 8 votes, would be nice to get some more.
TDE Recovery
This week’s TSQL Tuesday is being held by the mighty Kenneth Fisher – Backup and Recovery- see this link, how can I not get involved with this one?
I have been blogging for about a year now and have covered most of Kenneth’s bullet points – except Internals and SSAS based stuff, so I decided to do something different but still about backups. This post is all about the importance of backing up certain objects when you are using TDE – Transparent Data Encryption.
To understand TDE see my older post – https://blobeater.blog/2016/11/22/playing-with-tde/
So I have enabled TDE and I have a Full backup – lets restore it to a different server.
USE [master] RESTORE DATABASE [DWHDB] FROM DISK = N'C:\temp\dwh.bak' WITH FILE = 1, STATS = 5 GO
Cannot find server certificate with thumbprint ‘0xC0367AC8E9AE54538C17ACB0F63070D6FF21316A’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
The dreaded cannot find server certificate with thumbprint message. Why is this happening?
Well if you read my post on TDE ( or anybody else’s) you would have noticed the following message:
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
So the point is – back them up
USE master GO BACKUP CERTIFICATE MyServerCert TO FILE = 'c:\data\MyServerCert.cer' WITH PRIVATE KEY (FILE = 'c:\data\certificate_MyServerCert.pvk', ENCRYPTION BY PASSWORD = 'xxxxxxxx')
Then on the restoring server you will need to issue the following (assuming you have a database master key too)
CREATE CERTIFICATE MyServerCert FROM FILE = 'c:\temp\MyServerCert.cer' WITH PRIVATE KEY (FILE = 'c:\temp\certificate_MyServerCert.pvk', DECRYPTION BY PASSWORD = 'xxxxxxxx'); GO USE [master] RESTORE DATABASE [DWHDB] FROM DISK = N'C:\temp\dwh.bak' WITH FILE = 1, STATS = 5 GO
RESTORE DATABASE successfully processed 13002 pages in 0.444 seconds (228.770 MB/sec) – the restore works a charm.
All About Data 