This month’s T-SQL Tuesday entry hosted by Koen Verbeeck (http://sqlkover.com/t-sql-tuesday-89-invitation-the-times-they-are-a-changing/), a blog post about how we feel about the ever changing times within our technology space. Personally I love this new era of cloud computing and do not feel threatened in anyway.
I want to show you how I went from having multiple single SQL databases in Azure to a database elastic pool within a new dedicated SQL Server. Once setup I create and use elastic jobs. This post is long but I am sure you will find it useful.
A very quick post for today, I was navigating my way through the Azure portal within my SQL databases section and came across an edition that I had never seen before and I became very intrigued. It is called Premium RS (currently in preview mode) meaning we now have four tiers to select from – basic, standard, premium and premium RS.
If you ever need to move a copy of a SQL database in Azure across servers then here is a quick easy way.
If you have created a SQL Database in Azure (PaaS) and need to make a copy of it on the same server I will show you how via a quick method.
When connected to your master database execute the following: It creates SAP2 based on SAP.
CREATE DATABASE SAP2 AS COPY OF SAP
To track the progress you can use the following query.
SELECT * FROM sys.dm_database_copies
WHERE partner_database = 'SAP2'
Replication state 1 means seeding, this means that the SAP2 database has not yet completely synchronised with the original database. 0 means pending, which means that the copy is being prepared.
Once this has completed you can check sys.databases to confirm that the database has successful been created.
SELECT * FROM sys.databases
WHERE name = 'SAP2'
If you really want you can query sys.dm_operation_status to see the operations that have been performed on a database. I would expect to see some sort of operation around copying on my original database.
SELECT * FROM sys.dm_operation_status WHERE major_resource_id = 'SAP'
ORDER BY start_time DESC;
If you are interested in other Azure based SQL queries check out an older post of mine: https://blobeater.wordpress.com/2016/10/18/azure-sql-database-dmvs/