Question

Simon18 on Fri, 09 Jun 2017 20:14:27


Hi Forum,

Here is the issue we are having and hoping to ask an Azure expert regarding this

We are evaluating moving solutions from SQL server to Azure SQL and my interest is test automation since I am in the QA group.

One of the evaluation point is that we have many automation test cases that depends on SQL server snapshot functionality where we revert our test data to a previous known state during our test runs.    The snapshot functionality is very fast.

In Azure SQL there isn’t a snapshot functionality so we need an alternative solution according to (Search sql-database-features for azure).

We might need to depend on Point of Time restore and also Azure SQL copy database but both of those are very slow during our trial. 

The question is if we have many many test cases what is the most cost effective solution and the most efficient solution ?  Any suggestion is welcome.

Thanks

Simon


Sponsored



Replies

pituach on Fri, 09 Jun 2017 21:03:26


Hi Forum,

Here is the issue we are having and hoping to ask an Azure expert regarding this

We are evaluating moving solutions from SQL server to Azure SQL and my interest is test automation since I am in the QA group.

One of the evaluation point is that we have many automation test cases that depends on SQL server snapshot functionality where we revert our test data to a previous known state during our test runs.    The snapshot functionality is very fast.

In Azure SQL there isn’t a snapshot functionality so we need an alternative solution according to (Search sql-database-features for azure).

We might need to depend on Point of Time restore and also Azure SQL copy database but both of those are very slow during our trial. 

The question is if we have many many test cases what is the most cost effective solution and the most efficient solution ?  Any suggestion is welcome.

Thanks

Simon

Good day,

>> The question is if we have many many test cases what is the most cost effective solution and the most efficient solution 

If your test cases need to be execute in the same time using different versions of the database, then you will need to create those active databases. In this case I would probably use simple copy of the base database.

This can be done using simple query:

CREATE DATABASE Database1_copy AS COPY OF Database1;

* You can copy the database using Powershell and using the portal as well. Check this link for more information.

If your tests does not execute in parallel (meaning you only need one active version of the database each time), I will probably use a solution that save the history chain. In this case you can use File-Snapshot Backups as explained in this tutorial.

Another option which for most cases IMO is best for keeping history is not to based on the production and create backup for example but from the design each change that you do in the production you should do in the development environment first, and keep history using source versioning like GIT. This can be done by using DACPAC (Data-Tier Application Package) project.

Simon18 on Fri, 09 Jun 2017 21:55:08


Thank you Ronen for the awesome reply!  We tried the copy database option and it is very slow.  If we have thousands of test cases, we have to revert the database many times.  It is very time consuming to do this every batch of test cases.

As for point in time restore, our testing shows it is even slower compare to the Copy method you mentioned.

The file snapshot is only available in the VM hosting SQL Server 2016(IaaS solution).  We are utilizing Azure SQL(PaaS).

For the on premise solution we currently have, it was very easy because we could revert to a SQL snapshot and it is instantaneous.  When we run through our automation, it was simple and quick.  

I am wondering if there is another solution to do this.  Thank you for your help

pituach on Fri, 09 Jun 2017 22:47:25


hi,

You can use Azure SQL Database Point in Time Restore in order to restore to specific point i time base on the automatic backup. please check if this option fit your needs:

https://azure.microsoft.com/pt-br/blog/azure-sql-database-point-in-time-restore/

Simon18 on Sat, 10 Jun 2017 06:30:08


Hi Ronen,

Thank you again for your reply.  We tried both the copy and the point in time restore and they both have really bad performance and speed.  You could image if we are doing lots of these operations, our test automation execution time would be really slow and we cannot meet our SLA to provide fast turn around time for developers.

During our on premise solution, we were able to revert a snapshot instantaneously.

Any other suggestions would be appreciated?

Thanks