SQL Server unit test automation with VSTS - Team Services

2
votes

I'm implementing an Database Continuous Integration process using SQL Server , SSDT, GIT and VSTS, i'm quite new to it as well, so here it goes:

I can already develop in my local machine, sync the Visual Studio Database Project with my local Database using both "Publish" and "Schema Compare" features.

I'm also able to trigger a Build Definition in VSTS at each "Push" operation, that will build and then trigger a release to QA environment. Now i'm trying to put some unit tests that runs automatically after the build, though when the Build Definition runs, it cannot find any Database within the Build VM SQL Server Instance (the VM which is created every time you run an Build Definition)...

So i need to apply somehow my database schema to it, so it can run the automated tests through the "Visual Studio Test" Task within the Build Definition. I imagine that should be something like the "Publish" feature, or should i try deploying the .dacpac file using a PowerShell Script?

I've tried to follow this tutorial: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/perf-test/continuous-build-test-automation#test-integration-with-the-build

But i can't figure it out how to configure the Step 4 "Use "Database Sync" task" in the "Test integration with the build" section of the article.

Please, have anyone done this before?

2
visual-studiounit-testingautomated-testsazure-devopstfsbuild

2 Answers

2
votes

All right! So, i could make it work as i was expecting... You just have to use the MSBUILD Task with the arguments to publish the database like this:

/t:Build;Publish /p:SqlPublishProfilePath=your_publish_profile.xml

In your publish_profile File, you must point to the SQL Server instance within the VM inside VSTS. So i kept one Publish Profile file versioned for Developers pointing to localhost in my VCS, and another separated one to point to VSTS " (localdb)\MSSQLLocalDB ". It Doesn't matter the name of the database, cause it will create a new one with the name which you configured.

Then you just need to add the "Visual Studio Test" Task and Voila!

Be aware of one thing about the tests: It will point to the default building directory in VSTS, which is the one in the variable $(Build.SourcesDirectory)

I saw some people using the build argument " /p:OutDir " to get the .dacpac file to later use the "Copy file" Task to get in the Artifacts Folder.

But if you do it like this, you have to change the "Visual Studio Task" directory also, so i recommend to simply point the "Copy File" Task Source to $(Build.SourcesDirectory). Then let the "Visual Studio Test" Task with its defaults that it will run fine.

1
votes

You can use SQL Server unit test (run test through Visual Studio Test task) to verify database and the database could be deployed before test. More information, you can refer to: Verifying Database Code by Using SQL Server Unit Tests.

On the other hand, you can deploy SQL Server database by using Deploy SQL Dacpac Deployment group task or Azure SQL Database Deployment.