For those of you who have read some of my previous articles, you will know that I predominantly write on Data Platform topics. This is also the same for my presentations that I undertake. However as a Data Platform professional I need to step outside of my box of technologies and expand on relevant feature knowledge. I had heard about this tool called Biml – “Business Intelligence Markup Language” (If you have not heard of Biml you can learn more here.) and thought I need to have a look at this so I was better informed and able to have appropriate conversations with BI developers. With that in mind I am going to take you on the journey I took and hopefully get you to “Open you mind” and think “outside of the box”. In your day-to-day activities as a DBA have a think about the tasks that you are performing on a regular basis. With these tasks in mind hopefully by the time you get to the end of this article you have some ideas around how you could possibly use this tool to make things easier.
So let’s dive into our journey. There was a presentation, actually a double presentation on this tool called Biml, and knowing the two presenters I decided to go along and find out for myself what all the buzz was with the BI developers and this tool. As the presentations were progressing, I started thinking about some common categories of DBA Tasks:
- Database Maintenance
- Database Health
- Instance Builds
- Migrations & Upgrades
- Performance Monitoring
With each of these tasks in mind, I was thinking could I utilize Biml and make my life easier? Possibly. This started me down the path of putting together my first Biml Session. The easiest way for me to demonstrate this non DBA tool to DBA’s is utilising Database Maintenance.
With that in mind, let’s have a look at Database Maintenance. Now remember with talking about Database Maintenance I am NOT saying this is the only way you should be doing your database maintenance, but easily showing how the tool could assist you in your day-to-day activities.
Database Maintenance
In the simplest of terms, we are able to approach undertaking our database maintenance in four ways:
| Maintenance Approach | Pros | Cons |
| Maintenance Plans |
SSMS Integration Simple Easy Wizard |
Limited Functionality Not Exportable |
| SSIS Packages |
Expanded Tasks compared to Maintenance Plans Easier Reusability |
No SSMS Integration No Automated Scheduling |
| Customized T-SQL Scripts |
Greater Programing Control Easier Reusability |
Advanced Knowledge No Automated Scheduling |
| PowerShell Scripts |
Same As Above |
Same As Above |
With looking at database maintenance we can break this down into a number of core tasks: Database Backups, DBCC CheckDB, Index Re-Orgs / Index Re-Builds, Statistical Maintenance and Clean up tasks. All of these are able to be undertaken using any of the approaches mentioned in the table above. I am not in this article telling you which is the correct approach you should follow as there are many factors to be taken into consideration when you are deciding on your own approach. However in introducing you to this tool as a non BI developer these database maintenance tasks make for easily demonstrating what Biml can do for you.
Framework
Like anything in what we do, if we set the framework up first, we can achieve our goals easier. What I am taking you through in this article is no different. We need to setup a framework to allow us to use the tool for the tasks we have identified. For our framework to be useful we need to collect and store metadata about our environment in a repository of some sort.
Depending on your environment, for this article I have broken it into two types (Simple & Complex) based on the number of servers, configurations and Maintenance requirements. The first step for creating our framework is to collect metadata about our environment. The metadata would consist of things like but not limited to:
- Server Names
- Instances Names
- Database Names
- Database Maintenance Tasks
- Task Attributes
- Task Schedules
The easiest way for you to identify what metadata you need to capture, start by creating a simple maintenance plan that covers just database backups. When you are looking at the GUI wizard, make sure that you look at all of the options that are available for you to choose around your database backups. This would be things like (but not limited to):
- Backup Type (Full, Differential, Log)
- Databases (All DB’s, System DB’s, User DB’s, Specific DB’s)
- Backup Destination
- Number of destination files
- Compression Options
- Encryption Options (Depending on your version of SQL Server)
- Retention Periods
I have just shown you one example of where to start with identifying and generating your metadata. Have a think about all of the database maintenance tasks as mentioned earlier and work through the same process to identify for each task the options and configurations that you need/want to be able to use as part of your database maintenance.
Now that you have identified the metadata that you are going to collect and need to maintain, we are now ready to create the repository. Like all things in SQL Server you have multiple options and they come down your individual skill sets and editions of SQL Server you have. For example, if you have Enterprise or BI Editions you could consider using Master Data Services (MDS) as your repository. Alternatively, if you do not have those editions or are not familiar with MDS you can create your own relational tables. Initially when I started putting my first session together I was working with a colleague who is based in Colorado and we initially used Master Data Services and created a data model. As mentioned working in MDS does require you to have access to either the Business Intelligence or Enterprise Edition of SQL Server. Given my background, and looking at a way of approaching this without having to have the requirement for Enterprise Edition, I switched from MDS to creating a Relational Database. This way If needed, I could implement my framework in a SQL Server Express Instance.
Once we have the data model in place, we need to have a process that is constantly looking at our environment to check for any updates. Ie. have there been any new databases added to an existing instance? Have there been any new instances created? Have there been any instances that have been retired and no longer requires to be included in the regular maintenance? With the data model having been populated and constantly refreshed this is our source of truth and will be used as our source data for all of our Biml Scripts that will be generated (that we will cover a bit later).
BIDS Helper
Business Intelligence Development Studio (BIDS) as you may be aware provides you the ability based on what packs you have installed to create SSIS packages, SSRS Reports, Administer SQL Server databases. Now I do realise that BIDS changed and is now known as SQL Server Data Tool (SSDT) but for this article and specifically for this section I am treating them the same.
BIDS Helper is a free downloadable add-in available from CodePlex that caters for a number of versions of SQL Server. This free download allows you to start on your way with creating your Biml scripts.
BIDS Helper is your entry level into the world of Biml and creating your Biml scripts.
Stepping up from BIDS Helper we can look at a paid product called Mist. Mist provides more functionality and built-in automation around your Biml scripts and associated setup files. Initially as mentioned earlier, my first foray into this BI tool, I initially setup my Biml Packages in Mist. After this initial setup, I shifted focus to understand how this could be achieved with the use of BIDS Helper. If you are wishing to really get into using Biml and Biml Script for your environment regardless of whether you are using it for the traditional BI packages or like I am talking about in this article, Mist would definitely be worth the investment due to the amount of time saving it will provide. Your ROI will definitely cover the initial outlay. While putting together this article I was pointed to BimlOnline.com as an interim between BIDS Helper and Mist. While I have not used BimlOnline, I am including this reference for you to go and have a look at this tool.
Biml – What is it?
Biml utilises XML as a base scripting language to specify many of the aspects available in Business Intelligence Solutions that you would currently be creating inside of BIDS or SSDT. When you create an SSIS package and save it, you can go back and open the package (.dtsx) and review the source code which is in XML format. Now some of you may say this is very easy to read and navigate. For me I find it not that simple. This is where Biml makes it simple for me, because it creates an equivalent package but in a human readable form of XML. Biml also has the advantage of being able to deal easily with multiple versions of SQL Server from SQL Server 2005. Biml is able to achieve this by having some built in smarts that know the differences between versions of SQL Server and that some features are only available with a particular version.
To give you an idea of what I am talking about, if we were to create a Maintenance Plan in BIDS/SSDT our GUI representation would look something like this:
A nice and simple drag and drop approach to the creation of our database maintenance. Now we can see from above that we are going to perform a DBCC CheckDB followed by a Backup Database Task and finishing with a Backup Log task. Nothing really complex about what we are doing.
For simplicity purposes we are only looking at the “Back Up Database Task” shown in the package above. To create the same package using Biml our Biml package (Database Backup Task Only) would look like this when we open it:
As you can see this is really easy to read and understand what we are doing. We are taking a Full Backup of all databases on Server SMC-HDemo-SQL01SQL01 (Naming conventions do not allow for the in the connection string), using the default Instance level compression settings on our SQL Server 2014 Instance.
Our equivalent Biml Package to our SSIS package would consist of each of the tasks in the same human readable form.
BimlScript
The next step in our journey is to understand BimlScript. We just saw what a resultant Biml package looks like, but this is hardcoded with values. So if we had 100 Servers with a single instance of SQL Server in our environment that we are looking after, just like if we were to create database maintenance plans, creating a Biml package for each serverinstance combination would be very time consuming. This is where the power of Biml can make our lives simpler and easier when using BimlScript. This allows us to create and deploy packages to multiple servers with very little effort.
Now I want you to think back to the start of this article where I talked about Metadata for our SQL Server environments. This comes into play now. Take our Biml code snippet from just before and we can identify that certain parts that were hardcoded would be more effective if they were variables. BimlScript provides us the ability to insert code snippets into our Biml package that can access the metadata at compile time to generate a Biml package or multiple packages based on the Metadata.
To access the captured metadata and populate the newly created BimlScript, we need to add some code text to the start of our package to access the metadata. I achieved this by creating a SQL Statement that is accessed in a loop fashion to generate data in a memory table that can allow us to populate the variables.
We can see from the snippet above I have two nested loops creating two memory tables, 1 – Server, 2 – Instance. Given that we have the ability to have multiple instances of SQL Server on a server, I wanted to be able to retrieve the appropriate information per server per instance.
In the code snippet below we can see we are looking at replacing our variable data (anything inside of <# #> with metadata we are populating into the memory tables shown above. We can see that the Connection Name retrieves data from both the Server and Instance memory tables to allow us to generate the appropriate connection string name “SMC-HDemo-SQL01_SQL01” that we saw earlier in the Biml file.
This ability of BimlScript is where I see the huge power of this tool to be able to assist you in being able to deploy standard maintenance packages to your entire suite of SQL Server instances. By taking the time to setup your framework, you can have your regular refresh process running to create, update or remove maintenance packages.
Now that we have walked through the introduction to Biml & BimlScript and how once we have our Framework in place, we can quickly and easily create standard packages to be deployed to large numbers of servers and instances in very little time.
Now that you have read this article, I am hoping that the one take away you get is to think outside of the box about the activities that you undertake as a DBA and how a tool like this could potentially assist you in achieving more with less time. I would like to thank Reeves & Peter for assisting me in my journey into learning Biml, BimlScript, Mist. I would also like to thank Cathrine for reviewing this article.
Leave a Reply