-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Connecting to Azure SQL Database Primary Endpoint – Failover Groups

When you have setup a Failover Group in Azure for your SQL Databases connecting to the R/W (Read / Write) endpoint via SSMS (SQL Server Management Studio) is pretty simple, if you remember one little thing, which will be the discussion point for this blog post.

I wrote about Failover… Read more

0 comments, 140 reads

Posted in All About SQL on 19 October 2017

SQL Server: What will REPAIR do?

So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.

I was faced with the following message.

Table error: Object ID 1189579276, index ID 1, partition ID 72057594046578688, alloc unit ID 72057594053132288 (type In-row data). Page (1:441).… Read more

0 comments, 237 reads

Posted in All About SQL on 16 October 2017

When Things Go Wrong in Azure

Things go wrong in IT, it is no different with the cloud. When I say cloud I am thinking quite specific  such as the  underlying infrastructure that a company like Microsoft looks after for their Azure platform.

I took the below image from https://blogs.msdn.microsoft.com/psssql/2014/03/27/dont-rely-on-a-static-ip-address-for-your-sql-database/

It shows the many different layers… Read more

3 comments, 225 reads

Posted in All About SQL on 12 October 2017

Azure SQL Database Level Firewall Rules

I have written about Azure SQL Database LEVEL firewall rules before during my blog series, more specifically the security blog post. If you can’t remember the section on firewalls then I will bring the following diagram to your attention.

The main purpose of this post today is to discuss… Read more

1 comments, 725 reads

Posted in All About SQL on 11 October 2017

SQL Server 2017 Encrypted Backups And Compression

Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.

To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key.… Read more

4 comments, 1,418 reads

Posted in All About SQL on 9 October 2017

Azure SQL Database Data Integrity Checks

If you remember last month I wrote about DBCC CHECKDB and Azure SQL Database, more specifically whose responsibility (Microsoft’s) it is and ponderings on how it is actually done. (https://blobeater.blog/2017/09/04/dbcc-checkdb-azure-sql-database/)

Recall that is consumes a lot of DTUs running CHECKDB on your Azure SQL Database so I had… Read more

0 comments, 186 reads

Posted in All About SQL on 4 October 2017

SQL Server Tracking Tempdb Size

Here is a quick Extended Events script I knocked up where I wanted to track Tempdb file size changes for both the data and log file. I wanted to know who caused the tempdb growth, when it was done, what the T-SQL was and what sizes were involved. Not exactly… Read more

0 comments, 192 reads

Posted in All About SQL on 3 October 2017

SQL Server – Pause VS Stop

You have the ability to actually pause SQL Server, if you are in SQL Server Management Studio (SSMS), you might have noticed it as the below image.

As stated via official Microsoft documentation “Pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who… Read more

1 comments, 141 reads

Posted in All About SQL on 2 October 2017

SSMS 17 Dark Theme

Have you ever wanted SQL Server Management Studio (SSMS) 17 to have a dark theme? Seeing the below image (visual experience color theme options) really got me excited.

To do this you have to edit system files, don’t get me wrong, it obviously is not a supported means to get… Read more

0 comments, 268 reads

Posted in All About SQL on 29 September 2017

Checking Out SQL Vulnerability Assessment

Apparently there is a new tool from Microsoft where you can discover, track, and remediate potential database vulnerabilities. This tool is available for both on-premises SQL Server and Azure SQL Database. I actually cannot find the download for the on-premises version so I decided to give it a go in… Read more

0 comments, 897 reads

Posted in All About SQL on 28 September 2017

Azure SQL Database Active Geo Replication Changes

Six months ago how you would go about setting up Active geo replication for your SQL Databases would be different to today, yes things (naturally) do change but for this specific area it has changed for the better – again something that you would expect right?

So what exactly am… Read more

0 comments, 168 reads

Posted in All About SQL on 25 September 2017

SQL Server Tracking Suspect Pages

Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or… Read more

3 comments, 1,247 reads

Posted in All About SQL on 19 September 2017

SQL Server Schema Compare

A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of it.

First thing, you can find the download from this link: https://www.visualstudio.com/downloads/  and once installed (making sure that you select… Read more

2 comments, 178 reads

Posted in All About SQL on 18 September 2017

Azure SQL Database Resumable Online Index Rebuild

If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I… Read more

3 comments, 285 reads

Posted in All About SQL on 14 September 2017

DBCC CHECKDB Azure SQL Database

Who should be running DBCC CHECKDB for Azure SQL Database? Should it be Microsoft or should customers be scheduling it? All official information just tells you that you CAN run it (below shows the green tick) but still no clarity around the question.

I was exchanging messages with Azure Support… Read more

2 comments, 199 reads

Posted in All About SQL on 4 September 2017

SSMS and MFA support for Azure SQL DB

Quite a mouth full for a title but never the less very exciting. With the new version of SQL Server Management Studio (SSMS) 17.2 You now have the option to use Azure AD authentication for Universal Authentication with Multi-factor authentication (MFA) enabled, by that I mean use a login via… Read more

1 comments, 193 reads

Posted in All About SQL on 30 August 2017

[Video] SQL Server Deadlocks

SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.

In this quick video clip I create a deadlock… Read more

2 comments, 1,631 reads

Posted in All About SQL on 24 August 2017

Azure SQL Database – Scaling up

Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the impact of moving up and down tiers, in terms of your transactions and connections.

We should all be aware that when changing the service tier and/or performance level of a… Read more

1 comments, 977 reads

Posted in All About SQL on 23 August 2017

Azure Automation and SQL Index Maintenance

Azure does a lot for your SQL Database, from backups to automatic tuning but it still doesn’t have an index maintenance policy straight out of the box via the portal. Some may not care about rebuilding your indexes but it is still something I like to do, the question is,… Read more

1 comments, 189 reads

Posted in All About SQL on 21 August 2017

SQL Server – So you like to shrink do you?

If you do – shame on you and shame on me because I do.

Moving on, I found strange behaviour within SQL Server Management Studio (SSMS) version 13.0.15900.1  Look at the following screen shot; do you know what the issue is here?

Are you curious? Please read on.

First thing… Read more

0 comments, 224 reads

Posted in All About SQL on 16 August 2017

Older posts