I have been looking for several days on several forums, blogs, MSDN, etc., but so far I have not been able to find any recommendations on this topic. I will try to explain this message in more detail, because I think that the information and documentation on the development of SSDT are not well documented and there is no document with best practice, such as VS 2010 database projects ( http://vsdatabaseguide.codeplex.com/ ) .
I am a C # developer (no DBA), and we are at the beginning of the development phase of the project with a green field (10-15 developers), and we are currently defining our development process, including processing database development.
Used technology and tool chain:
- EF 5 (first the model, maybe we will change this to the database first, because problems like views, indexes, etc. are much easier to handle).
- SSDT (SQL Server Data Tools)
- VS 2012 / TFS 2012
- MS Test for automated unit / integration tests
The development process is based on test-based development and is as follows:
- Each function is developed by one developer in a separate function branch.
- Development and implementation of unit tests (= implementation of functions)
- If the function requires access to the database, the developer must a) create / update the EF model b) create the localDB database through EF "Create a database from the model" c) create / update the SSDT project by comparing the circuit d) create unit tests with using the test initialization method, which creates a new database and the corresponding test data for each test.
- Merge function branch back to integration branch
- After checking merge, CI assembly performs unit / integration tests
So, there are a few points at which I am not 100% sure about how to solve them (especially processing the database using unit tests), and I would appreciate it if you could put me in the right direction:
How to decide to create a database for automatic unit tests:
a) Generate a SQL script database (which can be manually created using the SSDT publish function) for each test method executed? This is an option that I would prefer, because each test has a clean and consistent database state for each test. Is there a performance issue creating a localdb database for each test?
b) Or use the msbuild task "SQLPublish" or "sqlPackage.exe"? I think this is not an option, because it will be one time, and I want to create a new test database for each unit test.
c) Or create a test database manually and save the * .mdf file in the root folder of the source code and create a copy for each test? But I would not prefer it, because developer A could override the file, which could have changes from another developer B, who used to check his changes. And that means the developer
How to solve the creation of test data for automated unit tests:
a) Run a test SQL script that inserts the corresponding test data for each test. I think this also means creating a new database, as indicated in paragraph 1. Again, this is my preferred option.
b) Or, using EF to create test data does not seem to be a clean way, as it depends on the implementation of the EF model, which must be actually tested implicitly through function block tests.
c) Or use manually created test database files. But this will make the development process more difficult for the developer. And it can also be overestimated by other developer checks.
It might be nice to mention what we expect from our unit tests. The goal of our unit tests is not to test the database schema, such as stored procedures, etc. We want to test parts of our application functions with the unit code tests, which can also be considered integration tests.
Do any of you have a similar development process and what are your impressions? Any recommendations for improving our development process? Are there any resources or best practice documents for SSDT development? And the most important question for me, how did you solve the automatic testing of modules, including the correct tests of processing and database integration?
source share