With databases in Fabric becoming Public Preview, I want to share a couple of gotchas and lessons learned.
1. Get started in only a couple of clicks
To get started with databases in Fabric, only a couple of clicks are needed. First, navigate to the Fabric workspace where the database should be created. In this example, a workspace called BiteSizeData is used.
To continue, click the “New item” button as shown in the screenshot below.
Choose “All items” and search for SQL Database. In the screenshot below I already added SQL Database to my favorites.
Before the database is created, a database name should be provided and to finalize the database creation, click the “Create” button.
As soon as the “Create” button is clicked, a database named BiteSizeDataDB is provisioned. This can take a couple of seconds, and as and result, a SQL Database, a default Semantic model and a SQL analytics endpoint is created.
Important to know is that the SQL database name cannot be changed afterwards.
2. Database Collation and supported database size
The provisioned SQL database in Fabric comes with the SQL_Latin1_General_CP1_CI_AS collation by default and a maximum size of 4TB for now.
3. SQL database development lifecycle
In typical database development, you will at least have 2 environments, a development, and a production environment (or workspaces in this case).
To move changes from one environment to another, there are a couple of options available, either you can use Deployment Pipelines in Fabric or you can use Azure DevOps Pipelines. Both are valid options, but be aware that if you choose to use Deployment Pipelines in Fabric, you need to take the following gotcha into account:
- The SQL Database name will be the same in both environments
Because of this, if you have your SQL Database in Fabric open, and you want to know in which environment you are working, you want to have clear Workspace names. To retrieve the current workspace where you are working in, you can either, look at the specific workspace name as shown in the screenshot below:
Or alternatively, you can use the settings button in the top left corner.
This button will open the “About” pane where you can determine the specific location you are working.
4. Integration with your favorite development tool
SQL database in Fabric supports source control and SQL Database Projects from the start. Which allows database developers to work independently via their database development tool of choice.
You can use SQL Server Management Studio to connect directly to the SQL Database in Fabric and make changes directly on the database. But working via Visual Studio Code and a SQL Database Project is supported as well. To connect to your SQL database in Fabric from either of these tools, you can use the provided connection information which can be retrieved directly from your SQL database in Fabric.
To get the connection information, open the SQL Database of your choice, and navigate to the “Open in” section in the toolbar:
This will provide you with both connection options:
After choosing the preferred development tool, the connection information will be provided:
Both options provide the same information so it doesn’t matter which option you choose.
5. Create your SQL Database Project
If you prefer to work disconnected, you can create a SQL Database Project based on your SQL Database in Fabric.
You can’t download the SQL Database Project from your existing SQL Database in Fabric. You must use Visual Studio Code, connect to your SQL Database in Fabric, and create a Database Project based on an existing database. In the screenshot below, you can see that the download option is not available when connected directly in the browser to the SQL Database.
Be aware that, if you connect to the SQL Analytics endpoint of your SQL Database in Fabric on the other hand, you will have the option to download the SQL Database Project.
This SQL Database Project contains the data structure of the SQL analytics endpoint and not of the SQL Database itself.
To give a specific example, I have created a table called Recipe in the SQL Database with the following CREATE statement:
After creating the table, I downloaded the SQL Database Project provided by the SQL Analytics endpoint which generated the following CREATE statement:
As you can see in the example above, all textual columns lost their precision and became varchar(8000).
This does allow us to use the SQL Analytics endpoint Database Project as a starting point to transform our data for analytical purposes.
6. Writing queries directly in the web browser
To finalize this post, I want to share a very useful functionality from my point of view. When you query the SQL Database directly from the web browser, every time you choose the “New Query” option in the toolbar, the query automatically gets saved.
In the example above, I created 5 different queries, and I renamed 2 queries to a much more readable name to use later on. The SQL query files only go away when you explicitly choose to delete them. This provides the benefit that I don’t lose queries unintentionally, but this also makes me aware to use this functionality wisely to ensure that it doesn’t get messy.
7. Monitor query performance and consumption
Thanks to the Performance summary which is available via the SQL Database view,
You can easily monitor what is happening on your SQL Database in Fabric. At first, it provides a high level overview with for example, CPU consumption, User connections, Requests per second, Blocked queries per second, Allocated size and Automatic Indexes
If you choose to dive into more detail, choose the View performance dashboard, and this can provide much more detail about how your queries are performing.
