T-SQL Tuesday #85- SSAS(MD) Backup Galore

This months T-SQL Tuesday is hosted by Kenneth Fisher(b|l|t) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is Backup (and Recovery).

Admitted, I was a bit challenged this past week as I decided to update my WordPress to a newest version 4.7. This however caused the admin module of my site to become unavailable. After creating a support ticket with Unoeuro.com I was guided to a blog post by Vashistha Pathak (b|t) which in the end led me to the solution, and I fixed it without the need of a backup. Phew… Kind of nerve wrecking not knowing if my site was backed up – fortunately I wouldn’t have lost a single character, according to Unoeuro support. That was quite reassuring and somehow coincidentally very fitting for this months topic, no?

My approach for this blog post is to accept Kenneth’s challenge from this tweet, where Kenneth triggered a thought stream using the word keys:

I immediately came to think of SSRS encryption key backup and I knew by then I had to try to cover all basics on BI solutions, this being SSAS, SSIS and SSRS as well. But honestly, I haven’t been able to set aside enough time to cover all. So without further ado, here is SSAS Backup Galore (the others will come in good time):

SSAS (MD)

For Analysis Services databases there are two (2) fairly straight forward options. However, you may want to discuss up front, how much effort you want to spend setting this up. Consider you already have a process for deploying updated cubes into production; In which scenarios would it be alright for the business to wait for a normal deploy, for the numbers to come back online?
In Brent Ozar’s (b|l|t) First Responder Kit there is a document where IT and Business need to state how quickly they would like to recover from different categories of “down time” i.e. Corruption, “Oops” Queries and so on. That’s actually still a good exercise to do, with both sides of the table, even though the document operates at a higher level.

Prerequisites
  • You must have administrative permissions on the Analysis Services instance or Full Control (Administrator) permissions on the database you are backing up.
  • Restore location must be an Analysis Services instance that is the same version, or a newer version, as the instance from which the backup was taken.
  • Restore location must be the same server type.
  • SSAS does not provide options for differential or partial backups of any kind. Nor log backups (should you be wondering in from the DBA world).
  • A SSAS backup contains only one (1) database, so no option to create backup sets.
  • SSAS can only have one fixed backup location at a time; It’s configurable, but not meant to be changed for each database.
GUI

We begin by connecting to the server where the database resides. Via SQL Server management Studio (SSMS) you then expand the Node Databases.
In my example here, I will be using a Database  named ‘Test Database’.

When right-clicking on the selected database, we get the option to Back Up.

This kicks off a wizard where we need to select a few options for our backup.
First of all, we can edit or designate the name of the backup file. One thing to note with regards to backups on SSAS is that only one designated backup folder can be active at a time. In the server options dialog, it’s possible to change the destination for backups, but only after adding the new folder location to the attribute AllowedBrowsingFolders, which is considered an advanced setting, so remember to tick that option in order to set the new location. Also, you need to do this by opening the dialog twice; 1st add the new folder to AllowedBrowsingFolders, close and reopen. 2nd change the BackupDir to the newly added folder.

My settings are just the standard ones, yours should differ in the sense that you should put your Data, Backup and Log on different disks for optimal performance.

Well, this is why you only get to type a name for the file in the next dialog and not the complete path and file name. You also get to select, if the backup can overwrite an existing file of same name. Tick if you would allow this.

The two other options I will cover in this post are Apply Compression and Encrypt backup file.

I have not met a setup where applying compression was not an option, yet. Obviously this has a penalty cost on CPU while executing the backup, and will affect the rest of the tasks running on the server (even if you have your data and backup dir on different drives). But in my experience, the impact is negligible.

This may not be the case with the encryption option, as this has a much larger foot print on the server. You should be using this with some caution in production. Test on smaller subsets of the data if in doubt.
Another thing to keep in mind, as always when dealing with encryption, do remember the password. There is no way of retrieving the data other than with the proper password.

In my example I allow file overwrite and apply compression.

When we clicking the OK button, the backup will execute and you will be able to track the progress in the lower left corner of the dialog. The dialog will close after successful completion. You have the option to stop the backup action from the progress dialog by clicking Stop action now.

After completion the file should be sitting on disk, where you designated it.

XMLA

Same options as mentioned above in the GUI section are available when doing your backups via XMLA.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Test Database</DatabaseID>
  </Object>
  <File>Test Database.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>9sse6</Password>
</Backup>

Connection to the server, start an XMLA query and fire away.
To leave out Password, just leave out the entire tag. Vice versa goes for Compression which you have to specify with False, in order to not have your backup compressed. In the above example, the backup is compressed.

Often I have seen this used within a SQL Agent job, that support SSAS Commands, just as the one above. Look for the SQL Server Analysis Services Command Step Type.

Obviously setting this up in a SQL Agent job is only half the job. After the backup has been done and/or scheduled, you need to ensure that the backup files produced are stored in a secure and available location.

PowerShell

Via the PowerShell cmdlet Backup-ASDatabase you can execute the same backup as described above. In fact this is a perfect example of why you should learn PowerShell, if your not already on that band wagon. Simply put, it doesn’t get easier than this. In my example, I will create a backup of all the databases on a server, in a couple of lines of code – Behold PowerShell AwesomeSauce (PSAS)

Import-Module sqlps -DisableNameChecking
sqlserver: | Out-Null
cd sqlas |Out-Null
cd <server>\<namedinstance>\databases
dir | Backup-ASDatabase

Remember to exchange your own values for <server> and if present <namedinstance>.
This will backup all databases on the given server.

Obviously you can extend this script with all the parameters exposed by the cmdlet. For more details, see this page.

Analysis Services Management Objects

The final method I will be covering is via Analysis Services Management Objects or AMO for short. AMO exposes, as PowerShell, a great deal of methods, where backup is one.
In order to use AMO to back up an SSAS database we need to get an create or load instance of said database through the object model in AMO. This can be done fairly easy.

The AMO classes/functionality comes with the Microsoft.AnalysisServices.dll which is installed with the product. In order to utilize it, we need to load it first or reference it, if you are doing a proper .Net project in SQL Server Data Tools or Visual Studio.

One thing to note before we go to the example is that the Backup method in the Database object has several implementations. Depending on your parameters, you can call different implementations of the same method. According to Technet, these are your options:

Backup(String) Provides programmatic access to Backup operations for a Database with file parameter.
 Backup(BackupInfo) Provides programmatic access to Backup operations for a Database with BackupInfo parameter.
 Backup(String, Boolean) Provides programmatic access to Backup operations for a Database with file and AllowOverWrite parameters.
 Backup(String, Boolean, Boolean) Provides programmatic access to Backup operations for a Database with file, AllowOverWrite, and BackupRemotePartitions parameters.
 Backup(String, Boolean, Boolean, BackupLocation[]) Provides programmatic access to backup operations for a Database with file, AllowOverWrite, BackupRemotePartitions, and locations parameters.
 Backup(String, Boolean, Boolean, BackupLocation[], Boolean) Provides programmatic access to backup operations for a Database with five parameters.
 Backup(String, Boolean, Boolean, BackupLocation[], Boolean, String) Provides programmatic access to Backup operations for a Database with six parameters.

I will show how to invoke the AMO functionality from <drum roll please…> PowerShell.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$Server = New-Object Microsoft.AnalysisServices.Server
$Server.connect(".\SQL2016DEV")

$BackupInfo = New-Object Microsoft.AnalysisServices.BackupInfo

$BackupInfo.File = 'Test AMO Backup.abf'
$BackupInfo.AllowOverwrite = $true
$BackupInfo.ApplyCompression = $true
$BackupInfo.Password = '9sse6'

foreach( $Database in $Server.Databases ) {
	$Database.Backup( $BackupInfo );
}

You should note that only via the method that takes a BackupInfo class as parameter are you able to specify a password.

Final thoughts

I think I have covered all possible ways of executing an Analysis Services backup – If I somehow missed and option, please let me know!

But as you can see, ways to back up your SSAS database are abundant and very flexible.

You should, as with any backup, on a regular basis restore and test them on a separate environment, to ensure that you can actually recover when needed.
Depending on data update frequency this can however be rather a complex setup.

Loading

#TSQL2SDAY #77 – Favorite SQL Server Feature – RoundUp

This week, I have been looking forward to the time where I got to read through all the contributions to my #TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).

So, in no particular order, I give you the round-up:

Rob Farley (b|l|t)

Rob writes about SQL Server being backwards compatible and why that is cool. Rob also encourages to upgrade, if you are stuck on older versions – it’s what Microsoft recommends as well.
I also like that fact that with Azure SQL Databases, you don’t need to make the decision – it’s taken care of for you!
Key Take-Away: “…we now recommend ongoing, proactive installation of CU’s as they become available.

Reasons To Upgrade SQL Server

Ben Miller (b)

Ben makes a great point in introducing Server Management Objects (SMO) – I like the fact that the post is kept as simple as possible, but still explains the why’s and how’s. If you are not aware of SMO, please do get going and Ben’s place is a good start!
Key Take-Away: I know SMO already, so no gain here…

T-SQL Tuesday #77- SMO is my favorite feature

John Morisi (b)

John must have balls the size of Texas. His favorite SQL Server feature is Replication! No, kidding aside. John offers a quick intro into what options there are with Replication and makes an effort to debunk some of the bad reputation Replication has. Do you wanna know more?
Key Take-Away: “…Replicate data to any ODBC or OLE DB accessible database including Oracle, AKA heterogeneous replication.

Favorite SQL Server Feature – Replication

James Anderson (b|l|t)

Has written about Partition switching. This feature is as cool as it sounds – and what even more cool, it’s quick! I have used this technique in my ETL, at times. Normally data can be moved quickly enough, but when you need it done ultra fast, this is definitely an option worth investigating.
James also provides a little insight into new features in the upcoming release of SQL Server 2016.
Key Take-Away: “The good news is that the SWITCH command works on regular tables and in any edition.

Partition Switching #TSQL2SDAY #77

Shane O’Neill (b|t)

Seems this is Shanes first blog post ever, so kudos for making the entrance on the #TSQL2DAY stage! Shanes first adventure in blogging hits it out with an excellent run-down of Common Table Expressions, and a recursive one that is!
Key Take-Away: “…you are SELECTing from the CTE while still defining the CTE!!

T-SQL Tuesday #77: Recursive Common Table Expression (CTE)

Andy Mallon (b|t)

Andy’s blog post is a challenge to the topic. A challenge which I think is utterly justified. Andy advocates to take a step back. Look at the bigger picture. Take a moment of thought, before you start hammering away with your new found hammer.
Key Take-Away: “…configuring a multi-subnet WSFC, with an AG that spans two FCIs, then take backups from the readable secondary to use for cross-domain log shipping to a third data center.” (who doesn’t eat that for Breakfast?)

T-SQL Tuesday #77 – My Favorite SQL Server Feature

Andy Yun (b|l|t)

Andy offers up Dynamic Management View/Dynamic Management Functions (DMV/DMF) as his favorite SQL Server feature. The blog post is a quick introduction to DMV’s/DMF’s and makes a great starting point for anyone who haven’t yet been using those. Andy also shows a couple of pointers, to what DMV’s/DMF’s has to offer. The information is practically endless…
Key Take-Away: “…DMVs/DMFs are the gateway into SQL Server!

T-SQL Tuesday #77: My Favorite SQL Server Feature

Jason Brimhall (b|l|t)

Jason has written an awesome post about the almost unmentionable feature. In the end however, he caves in and spills the whole deal. Unintentionally, I think, there are several take-away’s from Jason’s blog post – both the feature (which I will not dare utter), but also Synonyms – If nothing else, Jason demonstrates and example to follow, in the sense that the customer gets a perfectly working solution at minimum cost (even though an upgrade would have been way more cool to work with etc…)
Key Take-Away: “Using a synonym, I can extend this database beyond the 10GB limitation…”

Awesome SQL Server Feature

Kennie Nybo Pontoppidan (b|l|t)

Kennie offers another shot at CTE’s; This one also recursive.
Key Take-Away: “…we continually join the data set to the result set as it exists for each “loop”

T-SQL Tuesday #76 – Parsing code hierachies with CTEs

Edwin M Sarmiento (b|l|t)

Via a Dell Laptop, Edwin steers us through a good number of uses of SQL Server Logging. To anyone who thinks logging is just logging, have a look at the uses in Edwin blog post.
Key Take-Away:”How To Forecast Database Disk Capacity If You Don’t Have A Monitoring Tool

TSQL Tuesday #77 – Favorite SQL Server Feature: Logging

John Sterret (b|l|t)

John talks about Transactional Replication for Azure SQL Databases and offers three main reasons why this is an ûber cool feature. I have to agree, that this is a really cool feature and John does a good job explaining why. Looking forward to the follow-up post containing the How To.
Key Take-Away: “The reporting subscriber database can be scaled up or down as needed based on your needs…

Replication for Azure Databases – #TSQL2SDAY #77

Derik Hammer (b|t)

With the End of Support for SQL Server 2005, Derik finds it appropriate to pay his respects by picking Database Mirroring as his favorite feature. The feature will be replaced by Basic Availability Groups in SQL Server 2016.
Key Take-Away: “…mirroring fails over even faster than Availability Groups.

Favorite SQL Server Feature – #TSQL2SDAY #77

Kenneth Fisher (b|l|t)

In this post Kenneth walks us through the different applications of Delayed Durability in SQL Server. Advice on Simple and Full Recovery Mode as well as HA scenarios are covered. Delayed Durability is a new feature to SQL Server 2014 and applies to Azure SQL Database as well as the upcoming SQL Server 2016 release.
Key Take-Aways: “The fact that this should be utilized for massive Data Warehouse loads, where durability is not of concern.”

The Risk of Delayed Durability

I was really impressed by the hard work put into this round of #TSQL2SDAY, so thank you, to all the contributors. Great job all!

Loading

Jump into #PowerBI

tsql2sday150x150This months T-SQL Tuesday is hosted by Jorge Segarra (b|t|l) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is PowerBI.

I am hoping this blog post makes it into the summary, by Jorge. Admitted, I am a bit late. I only saw the invitation tweet today, and I was kind of lucky it was on topic, with what I was currently working with.

SSAS Performance Dashboard

My story with this half-baked product (the Dashboard you are about to see), is that I needed some way of tracking performance on a couple of Analysis Services (SSAS) query servers. There are a lot of good posts and talks about how to collect and store performance counters and SSAS logs out there, and I suggest you look into this, this or that, if you need inspiration.

The current data set is about 200K rows, as I am sampling each server every 5th minute.

The reason why I say this is half-baked, is that the querying part of the equation is missing. Currently I am only logging/storing Windows performance counters, and due to release procedures, I have not been able to implement the SSAS eXtended Events that gives us the link, to the queries run at any given time. Windows performance counters by themselves are not that interesting, because we can’t correlate them with specific queries. So we can’t really tell, what makes the servers go bunkers.

By Date

The By Date report is intended to let the end-user browse the data, based on the calendar approach to data. This is to identify peak hours, days etc.

By Server

The By Server report is to let the end-user easily distinguish which work load is related to what server. The rest of the break down is again based on calendar.

Brush

In this example the Brush Chart isn’t really fed the proper kind of data, but I put it in there, to let you see the possibilities with it.  Mark the lower chart to zoom in on the upper chart.

StreamGraph

This is also a very cool visualization, not sure it has any relevance to my data, but it looks awesome!

Final Thoughts

What I really miss for this kind of event based reporting, is a chart type that allows me to have a line for say CPU Utilization and on top of that mark events and their duration, by ie. a broken line chart or similar. Not sure how to correctly describe this, but kind of Gant-style on top of a line chart.

I have been working with PowerBI since it emerged, and I have been challenged to keep up with all the features and changes the PowerBI team has released during the last year. I am really looking forward to see what will be served the next year, even more so, because I will be spending more time with PowerBI now than before.

Loading

#TSQL2SDAY – Data Modeling Gone Wrong

tsql2sdayThis month marks the 72nd T-SQL Tuesday.  Adam Machanic’s (b|l|t) started the T-SQL Tuesday blog party in December of 2009. Each month an invitation is sent out on the first Tuesday of the month, inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week.
This month Mickey Stuewe (b|l|t) is the host and the topic is …

Data Modeling Gone Wrong

I am really looking forward to the other entries – because “cleverness” never seems to know any boundaries when it comes to Database Design or Modeling, and I just know there are some crazy things going on out there. So be sure to tune in to the host summary, that will appear on Mickey’s blog in the near future.

For my own part, I will refer to the latest “bright idea” that I came across. The application in question, was based on SQL Server, and was used to track price quotes. The application on top was in use at that point in time, and enhancements were developed, business heavily depending on this being “on-line”.

I general there were three (3) types of quotes. For each type, there was a set of table in the database that was almost identical. Only a some attributes where different, depending on type, but an inconsistent naming convention still gave away the major relations between tables in the database.
This meant that the application relied on three (3) sets of almost identical tables. This could most definitely have been designed differently, but it’s not the real cluster f**k of this application, so I will not go into detail here.
In every table of the database, there was a column name with a post fix ‘key‘. There was also a column name with a post fix ‘id‘ in all of the tables. At first sight that seemed to look like two tech leads having a ball. But actually it was not. I discovered later, that the had been only one “architect” behind this application. The good thing about that, was that the problem was easy to “contain”, since that particular employee was no longer working on the project. 🙂

After some investigation and data profiling, I slowly honed in on the fact that data in column blablabla_key and blablabla_id wasn’t related, in any way. Nor was key in one table related to key in any other table. Neither was id. In fact, there were no foreign keys defined in any of the tables. So no ref. integrity was ensured. In theory, even if I found the right match, crappy data could actually obfuscate this fact that there really was a relation.

Further investigation led to the conclusion, that id and key where in no way inter-related. So id in one table was not related to key in another table assembling the naming convention. No, it turns out, that for every quote, the business is operating with a term validity. The id of the respective validity, one (1) of three (3), is related to a specific quote key, which in no way was reflected, in any way, in the naming convention of either tables nor columns. E.g.: QuoteKey was related to ValidityId, in each of the respective quote type schemas. But that’s not it. For each of the three types of quota, two validities had been combined into one table. In fact, additional logic was to be applied, as if one validity was not present in said table, the other should take over. Sort of a fail safe validity. This meant that keys, if not present, was to be treated differently…

Oh Come on!

Needless to say, I spent a good amount of time trying to figure out how to combine the data, so we could do some proper business intelligence on top. Conference call after conference call left me fruitless, and still to this day, I am not sure i could have asked the developers any differently. The disconnect between me and them was soo huge, too huge to identify it seems. Asking one thing and getting a seemingly sane answer just made it even more difficult. Because none proved valid when querying the database. In the end, profiling the database senselessly, made the relations stick out.

So, to wrap up. Get serious about naming conventions, event if they seem to be a pain. Secondly, and more importantly, do define the relations in the database. There are several benefits of doing that, even though you will probably meet someone who will argue this.

As a smart exit remark, I wanted to end with a quote (only fitting for this post), but unfortunately I wasn’t able to find the original source for the quote. Did some extensive searching on twitter yesterday, without any luck. To my best knowledge, this was something Adam Machanic (b|l|t) supposedly should have said. Don’t kill me, if it’s not 🙂

“Performance doesn’t matter, when you have Dirty Data.”

What I read of off this quote is, that if you have dirty data, no performance in the world will ever make up for that. This is where Foreign Key constraints becomes your best friend. There are actually several good articles out there on Foreign Key Constraints:

Do Foreign Key Constraints Help Performance? by Grant Fritchey (b|l|t)
Foreign Key Constraints: Friend or Frenemy? by Mickey Stuewe (b|l|t)
Do Foreign Keys Matter for Insert Speed? by Jeremiah Peschka (b|l|t)

Thanks to Mickey Stuewe for hosting such a brilliant topic. I look very much forward to read the other entries!

Loading

T-SQL Tuesday #69: Encryption

tsql2sday150x150This months T-SQL Tuesday is hosted by Ken Wilson (b|t|l) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is Encryption.

The only requirement I have seen in my work as Business Intelligence Consultant, is that of the platform itself. No customer has ever requested any data encrypted, except for when data is to be exported to a movable device such as a USB stick or the like. And even then, it’s usually handled by some hard- or software solution in place on the stick.


Reporting Services is one of the tools that touches Encryption, and this becomes very relevant when you do migrations, which you will eventually have to do. In the current version of SQL Server, you have the following options to manage encryption keys in Reporting Services:

  • Back up a copy of the symmetric key so that you can use it to recover a report server installation or as part of a planned migration.
  • Restore a previously saved symmetric key to a report server database, allowing a new report server instance to access existing data that it did not originally encrypt.
  • Delete the encrypted data in a report server database in the unlikely event that you can no longer access encrypted data.
  • Re-create symmetric keys and re-encrypt data in the unlikely event that the symmetric key is compromised. As a security best practice, you should recreate the symmetric key periodically (for example, every few months) to protect the report server database from cyber attacks that attempt to decipher the key.
  • Add or remove a report server instance from a report server scale-out deployment where multiple report servers share both a single report server database and the symmetric key that provides reversible encryption for that database.

If you are running your Reporting Services in Sharepoint Mode, you should not that backup process does not backup encryption keys and credentials for unattended execution accounts (UEA) or windows authentication to the Reporting Services database.

Periodically changing the Reporting Services encryption key is a security best practice. A recommended time to change the key is immediately following a major version upgrade of Reporting Services. Changing the key after an upgrade minimizes additional service interruption caused by changing the Reporting Services encryption key outside of the upgrade cycle. And since every release of Reporting Services over the last decade has all been major, breaking changes, deprecating and almost no backwards compability, this will make good sense.

Luckily I haven’t been challenged on any Reporting Server installation, in terms of Encryption, yet. We are about to kick off a big migration of our environment here in Maersk Line IT, moving from Sharepoint 2010 to Sharepoint 2013, with Reporting Services in Sharepoint Integrated Mode. I have already made sure that keys have been backed up and we know all the password we need to know. Now only the easy part of migrating the platform remains…


Another product of the BI stack that makes use of Encryption is Integration Services. Koen Vereeck (b|t|l) has written on this in another TSQL2SDAY blog post, which can be found here. I am not going to write a copy of what Koen has written, It’ll suffice with a link to his blog post and a note not to use the default setting.
The most frequent issue I have met with this ridiculous default setting, is that when developers deploy their packages into a new environment, such as test, the packages fail – The Developers then yells, screams and pulls their hair, some even cry sobbing “It’s working on my machine”, until someone shows them the fine art of changing the ProtectionLevel property.

The current version supports the following settings (src):

Protection level Description
DontSaveSensitive No data on sensitive properties is saved
EncryptAllWithPassword Uses a password to encrypt the whole package.
EncryptAllWithUserKey Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS
EncryptSensitiveWithPassword Uses a password to encrypt only the values of sensitive properties in the package.
EncryptSensitiveWithUserKey Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package.
ServerStorage Protects the whole package using SQL Server database roles. This option is supported when a package is saved to the SQL Server msdb database. In addition, the SSISDB catalog uses the ServerStorage protection level.

This was my 50 cents on the topic of Encryption – Thanks for hosting Ken!

Loading