Anything that simplifies backup is something that I’m a big fan of—as a DBA it’s your job to make sure your company can restore its critical data in the hardware failure, user error, or natural disaster. So making the process simple and easy is a good thing right? Another thing that is important is sending your backups to a second location—in the event of natural disaster, or storage failure (please don’t tell me you are backing up your databases to the same SAN that they live on) it’s nice to have a DR site. In the old days, we would backup to tape and then ship the tapes off to secure location. In doing so we opened the potential for a lot more people to potential steal our data, and managing tapes is a nightmare. Many companies now are taking advantages of the cloud to archive their backups, or in some cases be their primary backup location. Both Amazon and Microsoft have really good (and really cheap—as low as a penny a gigabyte per month) options for doing this. As you might expect, Microsoft’s solutions are tightly integrated with SQL Server, and pretty easy to use.
In SQL Server 2014, Microsoft introduced two features that bring this cloud backup solution to the fore—backup encryption (independent of transparent data encryption (TDE)) and managed backup to Azure. Managed backup to Azure is also know internally, smart_admin (that’s the schema that owns the object which run this feature). You simply specify a retention period (in days) for your backups, supply a credential, and SQL Server does the rest. There are a few limits—currently system databases, and there is a maximum size of 1 terabyte, and your databases have to be in full or bulk-logged recovery modes. Aside from that, SQL Server determines the frequency and the type of the backups based on the workload of your database. You can find the full details of how SQL makes these decisions here.
You’ll need a couple of things to configure managed backup:
A SQL Server Instance with databases (if you don’t have those, why are you here J ?)
A SQL Credential
An Azure storage account
An Azure Management
The first thing you’ll need to do is create your credential—this is where you’ll need your Azure publishing profile. You’ll need to work with whomever manages your Azure account to get permissions to get permissions for the this task, but to do it we’ll break out PowerShell (Microsoft Azure PowerShell which you’ll need to download here. Run the command get-azurepublishsettingsfile
From there, Azure will launch a webpage (and may prompt you to login with your credentials). In my case, I was logged into my account, so my file was automatically downloaded. Now to Management Studio. Note—if you want to encrypt your database, you’ll want to create a master key and a certificate for database backup. Please do me favor, back these up some place safe, immediately. Better yet—put them in a container in your Azure storage account. You won’t be able to restore your encrypted backups without these, so it’s pretty important.
Ok—so now you have a publish settings file and a certificate. So you are to set up Managed Backup. First find it, expand the management pane in SSMS and right click on Managed Backup and select configure.
You’ll notice that this reports that you had selected an invalid credential, so go ahead and click “Create”. Another screen will popup here
Browse to where you downloaded your publishsettings file. Next click the drop down box and select the storage account which you want to use for this. The drop down will enumerate any Azure blob storage accounts to which your account has access. The naming convention for this account:container name—you container name will be $SERVERNAME-$INSTANCENAME. Go ahead and click create.
Now you can click the check to box to “Enable Managed Backup” and the “Encrypt Backup” checkbox. Select the certificate you created above, and choose your encryption, I’m using AES 256 here.
Now, that we have managed backup enabled, we need to setup a retention period. This will be a T-SQL operation, and we have the option to either set retention at the instance level, or at the individual database.
You’ll need the name of your certificate and the name of your credential. Your backups are now officially starting. One last thing—configure monitoring, so you can get notified when your backups are taking place.
That procedure with that specific input and parameter will setup your notifications and you are now good to go. My backup started.
Just before Christmas, Microsoft delivered a present to business intelligence (BI) geeks everywhere—we got access to the Power BI Dashboard preview, and additionally got a few new toys. A dedicated client that integrates all of the tools in the Power suite, and a new data gateway to allow us to refresh Power BI charts from an on-premises tabular model. In addition, to tabular Microsoft give us the option to connect to a wide variety of cloud data sources (see Figure 1), along with a new API for Power BI that enables developers to connect their own custom data sources with Power BI. All of these are great features.
Figure 1 Get Data Screen in Power BI Dashboard
Since Stacia Misner (b|t) and I are doing a precon on Hybrid Power BI in Nashville in a couple of weeks, I wanted to setup a demo environment. I thought this would be pretty easy—and the initial setup was. The first thing you need to do is download the Analysis Services connector from the Power BI website.
Figure 2 Download Analysis Services Connector
At first, I didn’t think too much about this. I had a small tabular database on my laptop and I tried to connect it. I’m not going to walk through the entire setup here (it’s pretty basic). When I went into Power BI to try get data from my on-premises source, I found the following error:
SQL Server Analysis Services
The Analysis Services you are trying to reach (or the gateway to it) appears to be down or your access is denied; please contact the publisher of the server! If this is an unexpected error, please refer to the technical information below!
Activity Id: be9f376f-ef6c-a916-054b-525699aaf4ef
Request Id: f986e722-308a-55ec-6377-173d6f5d5c88
Date: 2015-01-07 14:03:47Z (UTC)
I ran profiler against AS (which along with extended events are really the only way to get information from a Tabular instance)—and I got the following:
Figure 3 Error in SQL Server Profiler
I know my password was correct, as the client side tool makes a connection to AS and validates the connection. This initial configuration was running on my domain joined laptop, with single sign on (SSO) configured on the Office 365 side. I wasn’t responsible for the full setup of SSO and directory integration, so I decided to build out a test environment in my lab. This involved building a new Office 365 tenant, configuring Active Directory Certificate Services (ADCS) and Active Directory Federation Services (ADFS) to perform directory sync with Office 365. That’s beyond the scope of this post, but for more detail see Stacia and I’s white paper on the topic. So I did all of that—and then I got the following:
Figure 4 The Point When Joey punched his desk
At this point I started digging around the Power BI help—it’s pretty good, but it’s not very search engine savvy, you will have to search on your own. I came across a blog from Greg Galloway (b) where I realized the problem. Remember Analysis Services only uses domain authentication. And even though our directories are synced, I don’t have single sign on configured, so as opposed to coming in as email@example.com they were coming in as firstname.lastname@example.org.
Figure 5 Users Synced with AD
Greg proposes a solid workaround here—add an alternative UPN suffix and add those users to AS. This allows me to create users in the corp.contoso.com domain that have
Figure 6 Alternative UPN Suffix Creation
So I created a user in that domain:
Figure 7 User Creation
And then, everything worked.
Figure 8 Connection to AS Tabular in Power BI
There are a couple of things I’d like to mention here—and I’ll add (and I’ll post them to the product team)
There’s no way to edit/remove/add the connection from the Power BI site—it was only through the connector. This is inconsistent with the behavior in legacy Power BI sites. Also, it makes it difficult to verify properties in your connection (and the client lets you change a few things, but not the server name), so there is a 1:1 relationship between gateways and tabular instances
The error messages could be way more helpful—I realize this is challenging in a hybrid world, but links to guidance on troubleshooting would be helpful
After I federated AD with Office 365, I no longer had the option to login to my Office 365 in the initial setup process for the connector. As far as I know, this isn’t documented anywhere.
On Friday January 16th, I will be doing a precon for SQL Saturday Nashville with Stacia Misner (b|t) entitled “Finding Your Balance: BI in the Cloud, On Premises, or Both”. Stacia and I wrote a white paper for Microsoft last year on this topic, and we’ve continued to evolve the course material. This class will be aimed at a variety of IT folks—I’d really recommend it for IT managers, as we’ll cover a good overview of most of the cloud services available from Microsoft that are applicable to business intelligence (BI). For BI practitioners, in addition to the overview, we will demo Power BI, including the all new Power BI designer and dashboard features.
In addition to covering the BI side of data warehousing, we will also cover what you need to know from a network, active directory, and performance perspective to make cloud or hybrid data warehouse solutions work for you.
In this full-day workshop, you learn:
How to use Azure components to build a BI infrastructure completely or partially in the cloud
What changes to your solution architecture are necessary when you move components to the cloud
Best practices for configuring and networking Azure resources
How to integrate your cloud-based solution with your on-premises Active Directory (both Power BI and Azure IaaS and PaaS)
How to securely connect Power BI to your Azure and on-premises data sources
How to configure supported data sources for scheduled refresh
Best practices for administering Power BI components in Office 365
Watch our webcast featuring Meagan Longoria, Kevin Kline and Denny Cherry as they explore how to make communications clearer, especially during these stressful situations by improving your report visualization techniques.
As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.
And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.