December 16, 2003 at 2:21 pm
Hello everyone,
I am looking for some help.
I am responsible for five SQL2000 database servers. One is production, two for development, one for testing and one for development and testing of a sub-product. We also use Source safe. Although sometimes, depending on the activity going on on each of the servers, the roles of development and testing servers get interchanged.
The problem I face as a DBA is to co-ordinate with all the developers and the people testing the applications and databases. Stored procedures, triggers and schema changes are not consistent across the same database on different servers. Multiple copies of databases get created on the same server. This happens primarily because, once development has been completed, the database is moved to the testing servers. As the testing progresses and issues are resolved, the scripts are changed, etc. As there are many developers and people testing, it is just impossible to keep track of who is doing what and where the latest source code is. And especially creates a problem during implementation. Some of you may relate to this problem.
I am new in this position and would like to put a process in place where things are better organized.
If some amongst you would like to share your thoughts and experiences or what you have done at your place of work to make your life easier, I would highly appreciate it. Also, you could direct me to places where I can get some information on this subject.
Thanks in advance.
December 16, 2003 at 3:45 pm
Short answer:http://www.sqlservercentral.com/columnists/sjones/vcspart1.asp
Controlling access is the first thing you have to implement.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 16, 2003 at 4:26 pm
I think you should be more worry about what gets into production db and make sure what gets into works.
We usually create a lot of db for testing on different projects. However, we have one major test db for each production db.
We usually have two scenario:
1) Developers develop in their own db. Before going into production, I will create a new test db restored from production. In this case, the developers will have to tell me all the changes. Then I will apply them into the major development db and into the new test db restored from production. If everything is OK, I apply to production.
2. The developers develop in the major development db. Before applying to production, I will create a test db restored from production. I apply all the necessary changes to it. When everything is OK, I apply to production.
My boss, IT director, insists that I am the only one who can make production db changes so that we can have a better control of it. In another way, the developers are responsible for their own source codes!!
By creating the rules of testing against production db, this will force the developers to keep track of all the changes. Because, if they are missing something, their application won't work in the finaly test db restored from production. In this case, you don't have to worry about versions, but worry about what gets into production.
December 17, 2003 at 2:04 am
I'm the only DBA in a financial systems software house that faces similar problems. I have six SQL Servers in live which support about thirty or so databases ranging from a few hundred MB in a few tables to 10's of GB in a hundred or more tables. On top of that we have four or five SQL boxes in QA/Test and about eight in the Dev area. Not sure how that compares with others but that's my environment.
Like another respondent on this topic, among other things, all releases to live (and most to test) come through me. This could be something as simple as a patch for a couple of sprocs or could require a full code review for a completely new databases.
We find things are generally fairly manageable, mainly through religious use of SourceSafe.
We use a number of scripts to build a complete databases and these break down as follows:
Database generation
Tables
Logins
Users & role memberships
Default data (if any)
Then each UDF, view, trigger and sproc is maintained in a separate script of it's own - including all drop & create statements and any required permissions. We use a standard set of roles in all databases (e.g. READ_EXECUTE, WRITE_EXECUTE, NO_ACCESS etc) and only ever grant permissions to these roles, then add any users to these roles. This works really well because we can then ensure that permissions are included in our SourceCode - any new users just need to be added to the required roles.
We then have a master script which contains a number of standard bits (like the roles) and a set of #includes for all the composite parts, table script, sprocs, views etc. and a little app that one of our C++ guys created to pull eveything together into a single release script.
I then have a simple rule, I only ever release into test or live from SourceSafe. Any changes made to anything in Dev or Test without going through SourceSafe will not be included in the release - and that is the responsibility of the developer making the changes (not mine).
So if I want to change a stored procedure, I check it out of SourceSafe and make the required changes using my favourite editor (TextPad) then run it against the target database. When I'm happy that everything is done, I check the file back into SourceSafe and label it for release.
If the changes are more complex, e.g. new and updated tables and sproc & trigger changes. I create a new master script that contains the ALTER and CREATE table statements plus the #includes for any sprocs, triggers etc to be included in the release. Eveything is checked into SourceSafe and the master script is used to build the release patch - from the latest versions in SourceSafe.
We find this works pretty well for us.
If you need any more info or have any questions, raise them through this topic.
The other thing that's really useful is SQLCompare - http://www.red-gate.com/ - can't praise this cost-effective tool enough - especially the new & improved fast version.
Hope this helps.
. . Greg M Lucas MCDBA, MCSE
Edited by - gmlucas on 12/17/2003 02:07:35 AM
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
December 17, 2003 at 8:29 am
Agree with above: to expand on gmlucas, I appointed a junior DBA to release to QA. Each developer works from his own "sandbox" and there own DB. all changes go there. when they are done they create a single file for release to qa. (they just concat. all the files together, if they need multiple steps they name the files step01.sql, step02.sql, ...) When ready for release, the "release manager" runs only the concat'ed. files. If a problem is found, the "release manager" is not allowed to correct it, even if the problem is simple. This way we can be sure that the same stuff that gets QA'ed gets released.
Note: for each programmer and QA to have their own db, we did the following. we keep an empty db. that way when a programmer/QA needs an updated db, they just refresh from the "empty" copy. (they just run an ASP or JSP page that runs a SQL job to create/restore a db)
Curtis Smith
Application Dev. Mgr
DBA
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
December 17, 2003 at 8:35 am
Here is a process I followed when I ran projects that had production databases. Suppose you had a database in production that needed enhancements and it contained data that needed to be preserved. Lets call it 'myDB' running on a machine called 'prod'.
- Set up two additional machines, (e.g. 'dev' and 'stage') and create a complete copy of the 'prod' db in both of those machines using DTS or by transferring a backup.
- Have all developers make their changes in the 'dev' machine and test it to the point where you feel its releasable.
- Using RedGate SQLCompare, compare the schemas between the 'dev' and 'stage' databases. SQLCompare will generate an update script that would update 'stage' to the newer 'dev' schema if applied to 'stage'. However, before you apply it, have a meeting with all the developers to make sure the changes suggested by the update script are what they expected.
- If the script is good, apply it to 'stage'.
- Then use RedGate's SQLDataCompare to compare the contents of 'dev' and 'stage'. After the compare, turn off script generation for all tables except those whose contents that should be updated for development reasons (and not due to data changes in production).
- Apply the data update script to 'stage' and test it to your satisfaction.
- Apply any other scripts that were generated by developers such as those for initializing a new column in a table.
- Now you have scripts that will update the schema and the contents to make your 'prod' database be like the 'dev' database. And, by copying down the latest DB from 'prod' to 'stage' you can rehearse the script application process a number of times and do very accurate testing.
- When you are satisfied with the testing, back up the 'prod' database (in case something goes wrong) and apply the scripts to the 'prod' database.
I used this technique in a situation where me and my team had no access to the 'prod' machine, but the IT group would send me a backup of the 'prod' whenever I needed one. In this case, the IT group had their own staging machine and they would run the scripts against that machine first and we would do some testing. This assures that the IT group can be successful when they finally update the 'prod' machine.
You can compromise this approach by creating separate instances of SQL Server on your 'dev' machine and considering it a 'stage' instance. In my case, I used a second machine because I had DTS packages and related files in certain directories that couldn't be mirrored on the same machine. I had the IT group set up my 'stage' machine just like their 'prod' machine so it would be an accurate "ground simulator".
I also use SourceSafe to save all the full DB creation scripts that EM generates, and all the update scripts generated by RedGate and the developers. All of these scripts are considered part of the software/DB release and are labeled along with the rest of the source.
I used to be annoyed that the development group had no access to the production and IT staging machines, but I learned that having to create a release process for another group to run creates a level of discipline that pays for itself in the reliability of the releases. Once I realized that, I had my QA manager run the update process on our development 'stage' machine before we did our final testing and release to the IT group.
A disclaimer: I have run large software development projects for years, but I would not consider myself a professional DBA. I would really like to hear some comments about my process from professional DBAs, since I will be using this again in the near future.
Regards,
Chiefley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply