Moving Synapse Databases Between Subscriptions – Practical Guidance

One of the tasks, we often do with migration projects is move large volumes of data. Depending on how you are configured, you may need to do the migration project in a development or UAT environment as opposed to a production environment. This is particularly true if you have policies in place on your production subscription that don’t allow the individuals doing the migration and validation tasks to work in that subscription.

Just Copy It… Nope

So you can copy Azure SQL Database using the Azure Portal, PowerShell, Azure CLI, and T-SQL. However, this functionality is limited to Azure SQL Database and does not work for Azure Synapse databases (a.k.a. SQL Pools). Early in 2021, the ability to use the copy functionality to copy databases between subscriptions is also supported but requires security work to make sure the permissions in the database servers and networking allow that to happen.

You can find out more about copying Azure SQL Database in this Microsoft Doc.

Just Restore It… Nope

You can restore to the current server or another server on the same subscription. However, you are unable to restore across subscription boundaries at this time. If you need to move to another server in the current subscription, the process is straightforward, you can use the restore process in Synapse to restore to the current server using a different name. You can also restore to a different server in either the current or different resource group in the same subscription. The restore technique is used in our move process, so details on how to restore a Synapse database will be in the next section.

Let’s Move a Synapse Database

The process to move a Synapse database to another subscription requires some planning and pre-work. The first thing you need to do is create a new SQL Server in the same subscription you have your current Synapse environment. Because you can’t simply create servers, I would recommend that you add an Azure SQL Database to the server as a placeholder. An S0 should be sufficient to keep this server in place for what we are doing. DO NOT ADD anything to this server that will not be migrated. This is a temporary holding place for migrating databases. (This also works for other SQL Databases, but other options may work as well but are not the focus of this post.)

Now that you have the migration server created, the next step is to create a restore point. While this is not required because you can use the automatic restore points, creating a user-defined restore point is recommended. A user-defined restore point, allows you to choose the status of the database you want to migrate, rather than relying on the automatic points and trying to make sure you pick the right time (in UTC of course).

Once you have set the restore point, in the database you want to migrate, select Restore to open the panel to restore your database.

On the restore page, you have a number of options to complete.

  • Restore point type: Choose User-defined restore points
  • SQL pool name: This is not a big deal. The name is the database name used during the migration process and is not the final name used in the target server. Make sure it is something you know.
  • Restore point: Select the restore point you created for this purpose.
  • Server: Choose the migration server you created as the target.
  • Performance level: This one is more interesting. I typically choose a smaller performance level for this restore. Keep in mind that Azure needs to allocate resources to support the restore. Because this is not a final deployment, smaller may go faster. However, NO SLAs exist for this process. That means your mileage will vary. We have seen restores happen in 30 minutes one day and over 5 hours the next. It will be very dependent on the data center and how busy it is. This time variation must be accounted for in your planning.

The next step is to move the server using the Move operation on the server page. You have the option to move to another resource group or another subscription. In our case, we will choose another subscription. IMPORTANT: You will need Contributor permissions in the target subscription in order to move the server to that subscription.

After you have moved the server to the target subscription, you need to set a restore point for that database on the migration server. Then you can restore that database to the target server. It is very important that you use the naming convention and performance levels that you need for this restore as it is the final step in the process. Once again the restore process has no Microsoft SLA and as a result may take longer than planned. You need to have contingencies in place if you are working in a deployment window or have time restrictions.

Finally, you need to clean up the migration server. I would recommend either scaling down or pausing the Synapse database to give you a backup for a while if needed. Once the database is validated on the target server, you can remove the Synapse database (removes storage costs). I would recommend keeping this server as your migration server to use in the future. You can use this process to create copies of databases for development and UAT or similar needs from production instances.

Other Thoughts and Considerations

Here are my final thoughts on this process. First, the fact that no SLA on the restore process is provided by Microsoft has created issues for us in some cases. We have had to extend deployment windows during production deployments on occasion. My recommendation is to plan for the worst case and finish early if all comes together on time.

This process works! You can use it with other SQL assets and you can use it in multiple directions. Keep the migration server around so you can support other processes. If you clean most of it out, the cost of maintaining it is the S0 SQL Database.

One final thought, this is Azure. Thus, this guidance could change tomorrow. We have been using this for about 12 months when this was written. I hope this helps some of you move these databases to support your business and development needs.

Power BI: Making Date & Time Keys

Saving the Day from Delay Part 2

Creating DateKey and TimeKey columns can be done with built in functions in the Power Query editor. Quick call out, if you need the time along with dates, I highly recommend splitting your datetime columns in half – one date only and one time only. From there, you can use the same process to convert your time to a decimal number and use a Time Table for your time functions (GitHub link below). Below are some screenshots to walk you through the process.

Let’s say you have a datetime column like my Date column below. To start, I recommend going to the Add Column tab in the query editor, and select Date Only then Time Only to create two new columns. This way the new columns will be right next to each other in the applied steps which will make troubleshooting down the road a lot easier. Don’t forget, you can right click on steps and rename them to help yourself walk through and/or adjust steps in the future.

Time to make our keys! There are a couple ways to do this, but the easiest is to click on the calendar icon (or clock icon for time) and select whole number (select decimal for the time only column). If you’ve worked in Excel, this will look familiar. These whole numbers for date (or decimal for datetime) is the same across the two platforms and is what DAX uses in the background to process datetime equations.

Final Date and DateKey Columns
Final Time and TimeKey Columns

And that’s it! Next post we will look at how to join the date and time tables to your keys in the data model.

External Resources:
https://data-mozart.com/inside-vertipaq-compress-for-success/
https://github.com/AnytsirkGTZ/TimeTable_MCode/blob/main/MQuery%20Time

Power BI: Data Model Optimization

Saving the Day from Delay Part 1

Optimizing your data model can be a daunting task. If you read the intro to this series, you know one of the most efficient and sustainable solutions to a bogged-down data model is to remove native date queries and use a date table. This post will dig into how and why this will speed up performance in both refreshes and in the online PBI service and how to make date and time keys.

The key to optimization is compression. An efficiently compressed data model is a lean, mean, query running machine. There are two types of compression – horizontal and vertical. Horizontal compression occurs on a row by row basis while vertical compression occurs column by column. Power BI uses the Vertipaq Engine, a vertical compression model, to compress data inside the data model. While vertical is more CPU intensive, it is also more efficient as it finds the best option for compression based on the data type in the column (values/whole numbers are most efficient). Data mozart does an in-depth look on this process that I highly recommend reviewing if you have more questions (link at bottom of this post).

Vertical compression is significantly slower on date time columns than value columns. DateKeys are your best friend in compressing your data model because they allow you to capture vital date information but store it in a value format (the most optimal format – think whole number). Converting all your primary date fields to a DateKey will allow all calculations using that primary date field to run much faster as Vertipaq can process the requests more efficiently.

Next post we’ll cover making time and date keys in the Power Query Editor.

External Resources:
https://data-mozart.com/inside-vertipaq-compress-for-success/
https://github.com/AnytsirkGTZ/TimeTable_MCode/blob/main/MQuery%20Time

Saving the Day from Delay: Recap & Series Kick-Off

Ironically, I’m a few months behind on a recap for my presentation on building and using a sustainable, dynamic date query in M code – Saving the Day from Delay. On a positive note, the delay means that I can do a series on the importance of date tables, how to effectively use them in DAX, and what I mean by sustainable and dynamic practices. At the end of this post is a link to a GitHub containing my most up-to-date M Query referenced in this series and the PowerPoint used in the aforementioned presentations.

Presentation Recap

Using a date query, whether from a SQL table or Power Query table (M), can aid in optimizing and honing your data model. A main culprit in sluggish Power BI data models are the native date hierarchies. Native date hierarchies are the default setting in Power BI and provide you with hierarchies to use in visuals that allow for easy drilldowns through year, quarter, month, and day. You can turn off this default under File > Options > Current File > Data Load (see below). You can also do this in global settings, but I recommend doing this file by file as the benefits reach diminishing returns on datasets containing under a 500,000 rows.

Where to change your default settings

If you’re unsure what I mean by native data hierarchy, the image below contains an example of a date hierarchy built by Power BI. To rebuild a date hierarchy from a standard date table, drag and drop columns on top of each other to create a custom date hierarchy. By right clicking on the Hierarchy title, you can rename and reorder your hierarchy accordingly. One of the biggest reasons to turn off the native date hierarchy is that you now only have one hidden DAX table in your data model for the date instead of however many date fields you have in your model (I’ve seen some models with up to 15 date fields!).

Native Date Hierarchy

In subsequent posts, I’ll cover the benefits of turning off this feature, reconnecting your data model, how to customize the date query, custom columns in M, and best practices for a lean, mean data model machine.

https://github.com/AnytsirkGTZ/DateTable_MCode/blob/main/MQuery%20Calendar

I Wrote a Book – Hands-On SQL Server 2019 Analysis Services

While not the first time I have authored, this is the first book that I wrote as the sole author. Analysis Services is the product I built my career in business intelligence on and was happy to take on the project when I was approached by Packt.

I think one of my favorite questions is about how much research time did I put in for this book. The right answer is almost 20 years. I started working with Analysis Services when it was called OLAP Services and that was a long time ago. Until Power Pivot for Excel and tabular model technology was added to the mix, I worked in the multidimensional model. I was one of the few, or so it seems, that enjoyed working in the multidimensional database world including working with MDX (multidimensional expressions). However, I was very aware that tabular models with the Vertipaq engine were the model of the future. Analysis Services has continued to be a significant part of the BI landscape and this book give you the opportunity to try it out for yourself.

This book is designed for those who are most recently involved in business intelligence work but have been working more in the self-service or end user tools. Now you are ready to take your model to the next level and that is where Analysis Services comes into play. As part of Packt’s Hands On series, I focused on getting going with Analysis Services from install to reporting. Microsoft has developer editions of the software which allow you to do a complete walk through of everything in the book in a step by step fashion. You will start the process by getting the tools installed, downloading sample data, and building out a multidimensional model. Once you have that model built out, then we do build a similar model using tabular model technology. We follow that up by building reports and visualizations in both Excel and Power BI. No journey is complete without working through security and administration basics. If you want learn by doing, this is the book for you.

If you are interested in getting the book, you can order it from Amazon or Packt. From November 20, 2020 through December 20, 2020, you can get a 25% discount using the this code – 25STEVEN or by using this link directly.

I want to thank the technical editors that worked with me to make sure the content and the steps worked as expected – Alan Faulkner, Dan English, and Manikandan Kurup. Their attention to detail raised the quality of the book significantly and was greatly appreciated.

I have to also thank Tazeen Shaikh who was a great content editor to work with. When she joined the project, my confidence in the quality of the final product increased as well. She helped me sort out some of the formatting nuances and coordinated the needed changes to the book. Her work on the book with me was greatly appreciated. Finally, many thanks to Kirti Pisat who kept me on track in spite of COVID impacts throughout the writing of the book this year.

I hope you enjoy the book!