It’s quite common practice to keep databases on dedicated servers nowdays, especially if you use AWS RDS or Azure.
Relational databases performance is always painful and you might want to split data across at least a few databases. But if data is divided you still to have to do some logical operations across the whole amount and it’s quite simple, so let me show how it can be done using bot SQL Server Management Studio and CLI.
If you interested in Tableau installation on AWS you should have a look at CloudFormation templates from Tableau.
Single server installation suits well for trial, but it has a number of limitations including link to default VPC. But what if you want to deploy it in dedicated VPC or you don’t have default one?
Not a big deal, I’ve updated template and you can use it:
If you have MS SQL server in your environment and have to do some actions (execute migrations, change data, etc.) with it during your CI/CD it might be quite inconvenient to use Windows machine.
Fortunately we have sqlcmd for Linux, and Microsoft provides some instructions for popular Linux distributions – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools
But what if you have AWS Linux? If you try to use instructions from MS you will fail and there’s not much information across the Internet about this topic. The only useful link is here.
Since my env is highly automated I decided to create simple script to install sqlcmd on AWS Linux and share it with you:
In this post I’ll show an example of simple automation task – we need to download 7z archive from SFTP, unzip it and restore the newest database automatically on a daily basics.
If we go bit deeper we’ll see a few more requirements:
- SFTP requires auth;
- 7z file is protected with password;
- We need to test every important step;
- We need to add permissions to a database;
- We can shrink transaction log to save some space;
- We we need to log every important step to EventLog;
- We want to get a nice report with time measures;
It’s clear now, so let’s get down to prerequisites.