On many of my projects I’ve been using MS SQL Server as the database server. I’d install an instance of it locally and then connect to it during development phase. I did not want to develop with a database in the cloud, as it would cost me a few bucks a month and for what?
Of course, I’d have a staging environment which would connect to a database in Azure, so I can test if all is good with that connection and if all is good in a “production-like” cloud environment before actually deploying to the cloud. But I preferred my development to stay local.
But now I got a new laptop and I am thinking to myself: On my last laptop I ended up having 3 versions of MS SQL Server installed locally, each of them ended having 30 databases for various pet projects that I would never clean up… After a few years of not being really neat about keeping your MS SQL Servers clean (and on development environment I am not neat at all!), I ended up having a mess on my computer.
Now I have a shiny new laptop and am thinking of ways how not to allow this to happen. Hence, I will be moving stuff to the cloud as much as possible, to keep my shiny new machine clean. The downside is the cost – I’ll be discussing this in this blog post!
There are always pros and cons to every decision, so I’ll list my point of view here:
Taking a look at the fact that it is already 2022 (my God, am I that old already??) I decided that the pros outweigh the cons and decided to give it a go.
I have an existing MS SQL Server with the database I want to use for development. My goal is to move this database to the cloud (Azure). The easiest way is to create a
.bacpac file from your database.
In order to do this, in SQL Server Management Studio or Azure Data Studio right-click on your database and choose Tasks –> Export Data-tier Application. This will take you to a wizard that will guide you through the process.
When I did this the first time, it did not go smoothly, I got a bunch of errors! This is a good thing, as you get nice and clean explanations which help you clean up your database! For example, I got an error that one of the users set to have access to the database was orphaned for too long to be imported. A quick analysis showed that that user was left there from some test a while back and isn’t used at all – bye bye. Also, I got an error that a table had no clustered index. Again, a quick analysis showed that it was a temp table I used for some import and just forgot to delete – bye bye. I digress, but, in short – using the Export Data-tier Application wizard may help you clean up your development database before importing it to Azure.
Once this is done, you should have a
.bacpac file that we will use to import the database to our Azure SQL Server.
Azure portal has something that is called a Resource group. A resource group is basically just a level where Azure administrators can logically group their resources (DBs, VMs, storages etc.). It makes cost tracking and service tracking much more organized. If you do not have a resource group for development, let’s create one. First – navigate to the Resource groups:
Once you create a resource group, you can add different resources to it. In the main search box on top where it allows you to search for resources type “SQL Servers” and choose that. This is just a logical grouping for databases inside Azure. Create a server with necessary parameters. After you have created the server you do not need to actually create a database. One will be automatically created on that server once we import the
.bacpac file that we’ve created earlier.
In order to import a
.bacpac file into Azure we first need to upload it to a storage. If you do not have a storage, you need to create a Storage account in the Resrouce Group using the Create option.
Once it is created you need to upload the
.bacpac file there by any means that you prefer. Do note you should upload it as
Block blob, otherwise you might have some issues down the road. Then you need to use the database server’s Import option. The whole process is described well in a video on Microsoft’s official documentation.
Here I will outline some important steps. In your Resource group find your SQL server and open it. In the top menu on the Overview you will see an Import option:
During the import select the
.bacpac file which you had previously uploaded to a storage. Also, do note that, when importing the
.bacpac file you will need to select the Pricing Tier. I will discuss this in more detail in the end, but for development I choose the Basic tier (the default offered will usually be Standard). The cool thing about Azure is that you can always change this later on, but for development I want to keep it as simple as possible and as cheap as possible.
You will need to provide admin credentials, just to that the database can be imported. Once you click create, that is it! You have a database in the Azure cloud and you can use it for development purposes. You will find the connection string when you go to the database and choose Connection Strings in the left menu.
You could keep developing with a database in your local SQL Server and not go through all this hassle. After all, switching the connection from your local SQL Server to an Azure database is literally just changing the connection string. Everything else in your application remains the same.
So, why should one pay for the privilege of developing? After all, isn’t the point to actually develop and have other people pay you for that work?
That is the point and you don’t have to pay for the privilege of developing. But I don’t see it that way. I see it from a few different points of view which I’ll now try to outline.
I have bought licenses for Visual Studio. I am paying for GitHub. There a bunch of controls out there that can increase the speed and ease of your development such as products from SyncFusion or Telerik. There a bunch of cool tools out there that everyone of us uses and that cost money. If you choose not to pay for any of them, you just make your life more difficult. I already pay for some of them and I do use them in my own development for my pet projects and practice projects.
I dislike both DevOps and SysOps. I would love to be able to develop software and have someone else think about everything else. However, nowadays, if you want to be truly full stack, you need to understand the environment where your application will run and how. You don’t need to know every single wrinkle inside out, but you do need to know what is available and how it generally works, as it will help you make better decisions when architecting your software solutions.
Making my development environment as similar as the production environment will literally allow me to use it as a playground so I can learn more and more about not only what Azure has to offer, but also how exactly to do it and where to click. You can read the documentation, watch all the dotnetconfs and demo videos and read all the cool blog posts… Until you have clicked through something on your own and created your own setup, you will never have the feeling that you’re comfortable with something.
All my code is in GitHub. This allows me to easily access it from any machine. If I need to, I can set myself up on a new machine within minutes and just go on to development. But – I cannot do this if I need some data to work on and can’t get a hold of it. Keeping the development database in Azure allows me to start working on the code with data within minutes after sitting down behind a new machine.
I have not had the situation where I actually needed this, as I always carry at least one laptop with me. But, I can imagine that e.g. if you are on vacation and an urgent call comes in and you don’t have access to any of your development machines, being able to set up and just start working immediately is worth much more than paying $5 per month.
In Croatia, where I am from, we have a custom to drink coffee in caffe bars. Drinking coffee is a social event – you meet someone for coffee and a conversation – but it’s always coffee. And we do this with our friends on a pretty much daily basis.
So, $5 per month is three coffees in a caffe bar in Zagreb. I can survive putting that amount into making my development easier. If it were a more significant amount, I might even reconsider the decision. But, luckily, the salaries in IT are currently really good and I am not afraid to invest some of that money back into my own knowledge and into making my work easier.
I’ll go back to the pros and cons list I outlined earlier:
The pros speak for themselves. The cons? I am always online anyway, and in 2022 I can expect for this trend to continue (and it can only become even easier!). So I don’t consider that one a problem. Regarding paying for development, I think that money invested in myself and me acquiring new knowledge and making my work easier and quicker is money well spent.
So – my databases are going into Azure. If yours aren’t, I’d love to hear your reasons why. (And “I put mine on AWS” doesn’t count, Azure and AWS are a very similar concept, so by using AWS you technically do agree with me in principle, just not on the execution!).