March 17, 2005 at 10:20 am
My development environment contains two servers. One for developers to test and another (same structure different data) for the QA team to test.
The issue we run up against is when the developers have completed their cycle, they currently take the database and "pass it on" to the QA team. This means that any structure changes and data that the developers were working will overwrite the db that QA was working with.
I need to maintain the two separate servers for each team but the issue becomes maintaining the data the QA team has put together while incorporating the changes necessitated by the development team's efforts. What's the "best" way to do this?
March 17, 2005 at 4:04 pm
We script all changes from dev to QA. If someone adds a column, they generate an ALTER TABLE script. Stored proc changes are also scripted, with a DROP then CREATE and GRANT permissions.
It shouldn't matter what data is on which server, as long as it is in the format that the applications expect. Let the QA team manage their own data. If they run a test that modifies a lot of data, you are going to have to have a mechanism, whether it be DTS packages or a database restore from a backup, to rollback those changes to get the database back to a known state so that QA can run their tests again.
We have a code release document that is just a Word document that lists the type of change (proc, DDL, UDF), the object name, an identifier to the problem the change is solving, and a brief description. Then we store all documents in VSS so we can go back and see what we changed over time, and why.
March 17, 2005 at 11:45 pm
I agree with Dave, script the DB changes, document the changes, have QA review the docs and scripts, and finally test the deployment scripts on a copy of the production DB. I also use SourceSafe to archive the scripts and docs.
I added the following script to the library that I use to restore any SQL Backup file (Dev, QA, or Production) to any server:
http://qa.sqlservercentral.com/scripts/contributions/764.asp
Andy
March 18, 2005 at 6:38 am
Thank you Dave and ... David. I appreciate both your messages. I do in fact script out each change which generally speaking encompasses little more than added columns now and then and a few new tables here and there. Certainly nothing major as the application is an ASP model and the structure stays fairly consistent.
My concern however is that QA takes an inordinate amount of time to develop their test cases. So, when they find a "control" group of cases that works through the production DB, the last thing I want to have happen is overwriting their data.
I've considered keeping a copy of the database with the "control" data in it or even exporting each table's data to files that can be re-imported should changes take place. This way QA would have a constant data set that they know would work in the production DB. If you have any other suggestions for how this could be efficiently done, please let me know.
David A. Long: thanks for the link to the script. I'm not crazy about the use of cursors or temp tables but a few tweaks for our SQL 2000 platform and the script should be golden. Thanks!
March 26, 2005 at 7:50 am
Hi
As far as I understand, the main problem here is QA have their own set of data in a database and your main issue is to upgrade their database with all the changes made by the developers including database structure changes, changes to the stored procedures and changes to the triggers.
I do not know if you already found a solution.
What you can do is
1. Create scripts of stored procedures, triggers. When developers change any stored procedure or a trigger they just modify the script through query analyser and not the actual stored procedure . When they are finished, they should just run that particular script on the development database, this makes sure that the script is working properly. This needs to be done and followed for all the Sps and triggers.
2. You create a command file which will run through the command prompt by giving the QA database server name, DB name, user name and password. This command file will run all the SP script using OSQL command. You can get information about this in books online. So yourscript will look like
osql <some paramaters> script1.sql
osql <some paramaters> script2.sql
osql <some paramaters> script3.sql
One script each needs to be made for SP, triggers so that it is maintainable. Each time a new SP or a trigger is written add a row for that SP or a trigger in this command file.
3. When you want to upgrade the database on QA server, just get latest vesrions for all the scripts and run the above scripts. This will upgrade the Sps and triggers on QA server.
4. If any table structure changes are made on more script can be created for the same and can be run on the database, so that the database gets upgraded without disturbing the data.
creating of command files and alter table scripts is just one time activity and db upgrade becomes much simpler if above things are followed.
March 28, 2005 at 6:57 am
March 28, 2005 at 11:56 am
Alex,
You say "...So, when they find a "control" group of cases that works through the production DB..."
I work in the regulated environment. The test cases are mostly requirements-based. Anyway, QA in my opinion should not modify the database by development scripts. There should be a source control system that keeps the current pre-release (released by developers) unchanged when the QA department is testing. Preferebly not the development scripts that modify the database, but the whole database. I agree with Dave. If QA needs to modify data let them script all their changes and then apply them to pre-release. They don't have to roll back the changes, it is not reliable. The fresh copy of pre-release of a particular version is under the source code control and just needs to be restored.
Yelena
Regards,Yelena Varsha
March 28, 2005 at 12:47 pm
Yelena,
I agree 1000% ! Unfortunately, in a less "regulated" environment, QA has the option to make DB changes which are then pushed into Production. I fully understand the inherent problem associated with this and as far as I'm concerned, db structure should never change once UAT has started unless its beguun in Development and not QA.
March 28, 2005 at 4:32 pm
Alex,
Oh, well. Your work is like working with elementary particles (electrons etc) in physics: taking the measurements affects those measurements themselves because of wave nature of both.
In this case, it does look like a Merge Replication case. Did you think in this direction?
Yelena
Regards,Yelena Varsha
March 29, 2005 at 6:44 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply