Backup of an Azure SQL DB downloaded outside of Azure

From a dev colleague: “Asking for a client... do you know how to get a backup of an Azure SQL DB downloaded outside of Azure?”

Short answer - You can’t…

Medium answer - … and you don’t really need to for anything related to DR or HA. Backups are automagically handled in Azure, Azure SQL DB georeplication can be set up in a few button clicks. Instead of thinking of Azure SQL DB as a traditional SQL database in a server, think of it as a platform, consider spinning up a stand-alone copy of the production Azure SQL DB in a cheaper tier to set as pre-production. It’d be a better, truer dev/test/qa environment as a result.

Long answers – Okay, so you really want to get the Azure SQL DB to an on-prem SQL Server instance. Sigh.

1. You can manually script it out into CREATE and INSERT T-SQL statements – schema and data – with various tools, including a compare with SSDT, or an officially supported free crossplatform scripter that is Python-based. Keep in mind this might be a huge file and a lengthy manual process with a large database.

2. You could also use the “Export” button on the Azure portal for the Azure SQL DB to create a DACPAC on an Azure storage account. The file is locked with a username/password which you specify at the time of export.  It will take a few minutes, even for a small database. Once it's done, you can download the .bacpac file from the blob location with Azure Storage Explorer or something similar, and then import the .bacpac. Keep in mind that this might also be a huge file, and a lengthy manual process with a large database.

Actual Emails: What is a "soft delete"?

What's a "soft delete", and why should DBAs be aware of such behavior in tables?

So, a DELETE statement is a “hard” delete. The data is gone.

However, it is a common practice to build into tables a set of auditing fields…

For example:

CREATE TABLE dbo.whatever(
Id int identity(1,1) not null primary key,
WhateverInt int not null,
…
Createddate datetimeoffset(0) not null,
Createdby varchar(250) not null,
Modifieddate datetimeoffset(0) null,
Modifiedby varchar(250) not null,
IsActive bit not null CONSTRAINT DF_whatever_IsActive
DEFAULT (1)
)

The IsActive field, or anything similarly named like IsArchived or IsDeleted, is a common strategy to “soft” delete something and remove it from resultsets, but still retain history. All the queries on the table would then use WHERE IsActive = 1 to make sure they only viewed Active data, for example.

So, if you UPDATE a record to IsActive = 0 instead of DELETEing it, it disappears from queries and reports and screens, but it’s still there in case it’s useful. There may be some queries that intentionally want to query IsActive = 0 for historical or auditing purposes. 

Often, a filtered nonclustered index (introduced in SQL 2008) can be used to match the WHERE IsActive = 1, and then the index is smaller as a result and allows SQL Server to dramatically increase performance here. This is really only effective if a majority of the data has actually been soft-deleted.

For example: 

CREATE NONCLUSTERED INDEX IDX_NC_whatever_WhateverInt
ON dbo.whatever (WhateverInt) 
WHERE IsActive = 1

We had one client with an IsArchived flag (or similar) on a table with tens of millions of rows. Only 1% of the data was “active” and waiting to be archived. Their queries specified WHERE IsArchived=0, but still performed very poorly – lots of data still to be accessed. We added and modified some nonclustered indexes as filtered indexes to account for IsArchived = 0, and not only did this greatly reduce the size the indexes, but the query result time went from minutes to instantaneous. Huge difference. We could also have added similarly-filtered nonclustered columnstore indexes.

DBAs need to understand table design and recognize "soft delete" keys and the associated business logic in applications, because the Missing Indexes feature and other common query tuning tools won't include them in recommendations for filtering.

Furthermore, we can even enforce filtered uniqueness using a unique nonclustered index. In this way, we could make sure that there is only one active record for a given key set, but allow many inactive records for the same key set!

No Speaker? No Problem! Host a non-traditional User Group meeting

Was honored to speak to fellow User Group leaders at the PASS Summit User Group leader meeting on Tuesday. Here's detail and links to some of the things I mentioned. Thanks to everyone who chimed in at the end provided their own ideas to the room of user group leaders from around the world!

First, consider having a joint meeting with a shared speaker with other user groups, like a .NET or Analytics UG in your area. Share sponsor, food, speaker, networking. More critical mass of fellow professionals, more ROI for sponsors, more potential future speakers and volunteers.

Second, make sure you ABC - Always Be 'Cruiting - new speakers, attracting people to get their feet wet for the first time with technical speaking. Make sure the atmosphere of your group remains approachable, constructive, and not intimidating to new speakers. Make sure you are always reaching out directly to new speakers, offering short-format speaking timeslots at your meetings, and offering mentoring and constructive feedback.

Non-traditional meeting ideas: I'm not claiming credit for creating any of these ideas, they've been executed by others for sure, in fact, I've tried to link to them when possible.

• "Game shows" - Most fun if game shows actually have individuals at play, not just "ask the crowd" style
• SQL Jeopardy! using PowerPoint - Not hard to make at all, just what it sounds like. Don't forget to phrase your answer in the form of a query!
• I have a slidedeck for three rounds of Jeopardy! from SQLSaturday Baton Rouge 2016, happy to share
• Here's another Jeopardy! game shared by the Baltimore SQL Server User Group
• Here's another from Chad Crawford of the Utah SQL Server User Group
• SQL Family Feud - Chad from the Utah County SQL Server Users Group also put out an online survey and delivered the data to anyone who requested it in January 2018. - SQLSat Dallas did it this year for a big finale as well, or, ask around to collect your own "surveys"!
• There are a lot of list games that are fine for group play, including SQL-themed games in the style of Listography and the Game of Things, like topics like: write down as many data types as you can in the next 20 seconds, or "things developers say", or "most common table name"!
• Lead an interactive Normalized Database Design for a topic that many people know the business rules for, or, for a brand new business case with a guest product owner - https://www.sqltact.com/2013/01/try-database-design-exercise-at-your.html
• Lead an interactive "choose your own" Database Corruption Recovery Challenge from Steve Stedman's blog series - http://stevestedman.com/server-health/database-corruption-challenge/
• Speaker Idol - self-explanatory - well in advance of the meeting, put the call out for "short-format" speakers only, ask bosses, sponsors, spouses etc. to be judges.
• Licensing - this is not a common topic - ask a local software reseller to send a SQL licensing specialist to talk and do Q&A for an hour, can be very informative.
• The Toolbox - ask everyone in advance to bring handwritten tsql or powershell scripts from their own "toolbox" to share and briefly demo
• Networking Night - invite a wide array of people to give short-format talks on career, interview, soft-skills, invite attendees to bring resumes. In BTR, we also add this meeting as a Gold tier SQLSaturday sponsorship
• New speakers night - another short-format idea, invite only new speakers to give talks, with experienced speakers primed to give constructive, positive feedback
• PASS Summit knowledge transfer - invite everyone who attended the PASS Summit to give a recap/best-of style presentation to do knowledge transfer to everyone else.
• Panel of Experts - again, make sure your group remains constructive, approachable and non-intimidating. But a panel of "experts" for Q&A might be a great idea to attract user group attendees with name recognition. 

@william_a_dba speaking about his Baton Rouge UG experience at #PASSSummit pic.twitter.com/quT8nOEwIJ

— Paresh Motiwala (@pareshmotiwala) November 6, 2018

Great alternative meeting ideas from @william_a_dba too! https://t.co/6ksdsfap4g

— Matt Gordon @ #PASSsummit (@sqlatspeed) November 6, 2018