Managing Data On Premises and in the Cloud based systems differs in many way. On Premises, you can run the same database software configuration and environment very long time. These kinds of systems are very stable environments. But in the other hand, you can make changes in all elements you want.
Cloud based environments are continuously changing. New features, techniques and tools are published all the time. Some features or options may can be taken away almost without any warning. Some new thing may supersede the older one, etc.
There are lot of automations for cloud databases which makes your life easier. You can choose automatic backups, version upgrades, failovers (in some cases) and basic monitoring features. But still today these automatic features are significantly limited compared to On Premise setups.
Even there are a lot of automation, remember that you still need to do database monitoring, maintenance, upgrades, security, backups, HA/DR, tuning, etc. – to meet your SLAs. You simply can't just leave the databases on their own. If you just trust that the automation will handle everything, you probably get a poor performing database which you will try continuously upgrading to bigger instance - and pay more for it.
Main topics when maintaining the Cloud Database
The base installations are usually done with readymade templates, but with cloud VMs you might need to do the manual installation. Upgrading to bigger instance is usually easy and quick. You can also downgrade the instance if you don't need bigger capacity anymore. Supported and available database versions and vendors can be varied and depends on cloud providers. Usually new versions are available quite quickly. There are also some cloud specific new database products available, for example CosmosDB, Aurora etc.
If you migrate database from On Premise to cloud you probably need to do some kind of compromise with instance type (you probably can't get exactly same database environment you have used On Premise)
Usually you will get the basic instance and database monitoring and database from cloud provider as default. But this is not enough in many cases, it is better to have more detailed database specific monitoring above this basic monitoring. Having a valid monitoring setup, you can manage several different cloud database environments in centralized way. With valid monitoring data, you can get better database performance (tuning can be done proactively) and avoid possible outages.
There are automatic upgrades, typically for minor versions, for cloud databases. If you allow automatic upgrades, be aware everything it might affect. You have to schedule those and check that after upgrade everything is still working and running as expected (including the applications). The major upgrades are better to do manually because they can negatively impact application performance.
Database backups are done by snapshots and you have to make schedules for them. And remember to do restore test for these regularly. Backup retention times need to be considered because default retention times are quite short. If you need dump backups you can do them also but you need do these manually. With these you can restore schema/table level backups. Prepare server where you take these dumps and storage where you store them (blob storage for example).
In cloud, you have to consider High Availability and Disaster Recovery in same way as you should in On Premise. Usually it's better and easier to use cloud providers own techniques althought there are some other ways you may consider in some cases. Cloud HA techniques are substantially different compared to On Premise techniques. Depending of the database software used there are some limitations how to implement these issues. Failover and Disaster Recovery testing is part of good HA practice and design.
There are several levels of security in cloud environments. You can give permissions on database instance level and database schema level. The database instance level accesses are granted usually via security groups or via resource groups. The databases can be public, these can be seen over public internet, or private, these are in private subnet with private IP. SSH connections are supported, but how you config them depends of database you are using. The schema level security is basically similar you have On Premise. But there are some differences and limitations in Cloud databases.
In general, we can do SQL level tuning and general database server (virtual machine/instance) tuning. When we migrate the database to the cloud there may be still need for tuning issues. If we are doing more detailed monitoring for databases we can also implement better changes using valid metrics - rather than using "guess" method. Detailed monitoring also helps when you need to do problem solving for example when application is responding poorly. There are limitations in cloud databases which parameters can be changed. Also, when migrating database from On Premise to cloud, the hardware is changing and usually you may need some kind of tuning; SQL or virtual machine. That's because there may be some bad SQLs which worked On Premise but don't in cloud because the environments are not comparable. Database server level tuning can be also mean setting up a new bigger or better performing instance. There's no specific tuning tools in Cloud. You have to use other methods and tools (cloud, third party or tailored) which are suitable for in that case.
Usually the target is that the database in cloud is working 24/7, and the end users does not see any outages at all. Migrating databases in the cloud will not mean that there is no need for Database Administration. There are still a lot of things that you have do. You have to adapt new ways of doing the database administration. You have to learn a lot off new tools and methods to work with cloud databases because cloud providers have different approach of these things. And especially, cloud environments change rapidly. We have cloud database experience and have done the database migrations to cloud for our customers. Continuous developing the cloud base database monitoring solutions is part of the work we are currently doing.
Simo Kemppinen, Senior Database Administrator
Jarmo Väänänen, Senior Database Administrator
Authors have several years (Jarmo more than 25 year and Simo more than 15 years) experience of database administration on different enterprise environments.