February 21, 2011 at 9:48 am
I'm going to have a new job as a dba.
The new place has some vendors softwares using sql server, and also we have 5 in house developers to develop database applications hosted on sql servers too. Since the last person who worked here has left before me, I have to figure out some procedures.
I have a question for the work flow.
What is the tasks related working with develpers? for example I know developers will usually create their own databases on the developer server, then how the databases move to production server?
Do they do that, or dba do that, using backup restore, or using scripting databases? or some other steps involved?
Thanks
February 21, 2011 at 10:40 am
It's very, very rare for an entire database to move from development to production. More common, just new objects, just changes.
The advice that Grant gave you earlier (http://www.sqlservercentral.com/Forums/FindPost1066900.aspx) applies
P.S. in a well run environment developers have no rights at all to production. Anything else is asking for chaos and trouble.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2011 at 10:47 am
It really depends on how you work with the developors. Personally, I try to work on the database the same way they work on code, putting it into source control, only labelled deployments, tested deployments, all that stuff. Presumably they may already have a process. I'd find out what that is first. If they don't have one, for example, they're developing against production, you might have your work cut out for you. But there are so many ways you can work with them, you really need to talk it over with them. There isn't one approach for all situations, although, getting your DB into source control is pretty universal. I've worked with pure Agile teams, pure water-fall teams, and everything in between. The details of the interaction are frequently different, but the approach is generally the same.
As the DBA, your primary responsibility is the protection of production data. Work your processes backwards from there. Every time something comes up where you have a choice, the first question should be, does this put productoin at risk? If yes, then you eliminate or mitigate that risk. Proceed from there. After that it's a question of making the process easier, more automated, repeatable, more efficient, but with the constant goal, protect production data.
Like I said before, lots of this, and more, is written down in the Red Gate Guide to Team Development. I'm pretty sure it's free. Get a copy & read it[/url]. It really should help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2011 at 10:55 am
Good advice from Grant, but I might argue that the code and work being done in development is important and it's a "kind of production" level of stuff that you don't want to lose.
From that perspective, I try to work with developers to make sure their databases are backed up and protected, just to prevent issues from popping up in the future.
February 22, 2011 at 2:10 am
GilaMonster (2/21/2011)
P.S. in a well run environment developers have no rights at all to production. Anything else is asking for chaos and trouble.
I'd argue to give the ETL team read rights in production.
Sometimes the development environment doesn't have access to the full sources, so unexpected data quality issues can emerge in production.
It is nice to be able to check if all the data has been nicely loaded into fact/dimensions tables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2011 at 9:08 am
This can be helpful (developer read in production), but it also produces another attack vector for security. Developers being able to pull that data out, means potential breaches, especially if you start to count contractors.
I would argue that developers ought to be able to request counts/tallies from the production system and submit queries to be run on production, but not necessarily have their own rights to do so.
There's also the problem I have seen where developers mis-write a new query to "check" something and include a cross join, impacting the production workload.
February 22, 2011 at 9:22 am
i create a release environment for my developers to get production data
There are too many times someone writes a query that shouldnt hit production, plus it is easy then to use a script to remove or change secure data.
I had end users using excel to write sql queries against production
February 22, 2011 at 1:06 pm
Koen Verbeeck (2/22/2011)
I'd argue to give the ETL team read rights in production.
Disagree. But then my main experience is in a financial environment. The data in those tables is highly confidential and worth a lot on the market.
What I would say is that the developers can have a dedicated 'production check' account that's highly monitored and locked most of the time, that has read access to the tables that they need to check ETL tasks. If they need, they can just ask for the account to be enabled, but then they know that everything they do is audited.
Read access for developers in prod also often leads to them running ridiculously complex queries (or ad-hoc, unauthorised data extracts) during business hours impacting the performance of the system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2011 at 11:55 pm
GilaMonster (2/22/2011)
Koen Verbeeck (2/22/2011)
I'd argue to give the ETL team read rights in production.Disagree. But then my main experience is in a financial environment. The data in those tables is highly confidential and worth a lot on the market.
What I would say is that the developers can have a dedicated 'production check' account that's highly monitored and locked most of the time, that has read access to the tables that they need to check ETL tasks. If they need, they can just ask for the account to be enabled, but then they know that everything they do is audited.
Read access for developers in prod also often leads to them running ridiculously complex queries (or ad-hoc, unauthorised data extracts) during business hours impacting the performance of the system.
Ah well, we have different backgrounds 🙂
At my last project the production data was not that confidential and worth a lot. (but that isn't an excuse of course)
However, most of the reporting on top of the data warehouse was on SSAS cubes, so querying the SQL Server database didn't really pose a thread to end-users.
So my opinion as a BI developer:
* either give a representative set of production data to test on, so we can verify data quality
* or allow us to check it in production if the first isn't possible. How this is done, I leave that to the DBA pro's 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 1:35 am
Be obsessive about backups and restores and don't forget the master,model and msdb and reportserver databases. If you can't restore a production database from a very recent backup you're likely to lose your job.
Make sure there are seperate development, test and live environments and stick to the principles of ITIL - get argumentative developers sent on the ITIL Foundation course :-). Developers work on development, move it to test and they and the users test it then move to production when OK. Plenty of backup and restore opportunities in that as you will frequently be restoring live to test or dev - don't forget to change the recovery model to simple as well. Get paperwork for releases - a list of changes to be made to the database and signed off by testers and development manager and change control.
Keep a directory of scripts for the jobs, especially backup and restore and put them into source control and back them up as if they were source code. If you have scripts handy for "restore last full backup on server x" and "restore database y on server x to point in time" it is much less of a panic and you have a starting point when someone needs something done in a hurry.
Practice these restores to a test system or different database name so your scripts are tested.
Make sure you understand recovery modes and the log backups needed.
Add to your library all those useful scripts from SQL Server Central for checking things and once you get into the job and backups and checks are automated (sql agent and report server subscriptions are useful here) then you should have a pleasant life. Good luck
February 24, 2011 at 7:50 am
Thank you, P Jones. I understand DBA will do quite a lot restore to developer or test server.
I have a couple of questions, when you say change them to simple recovery, does it mean after restore, change the databases on testing or developer machine to simple recovery mode, because we don't need to do backup for these databases?
Secondly I see you said "Developers work on development, move it to test and they and the users test it then move to production when OK. " Do developers move the db by themselves to production?
If not, hat is the procedure for you as a dba to move database from testing to production and make subsequent changes for the databases as days go on?
I think what I may do is to do a backup and restore to production server the first time. Then later if they have small changes, like add index or columns, developers will request me to do it on the production server. Is that correct?
I havenot been on the job yet, not knowing details about that, and I was just told we have 3 developers, and I'm not sure if there is a source control system for database existing in the enviroment.
February 24, 2011 at 8:24 am
I strongly recommend you take a look at the Red Gate Team Development[/url] book. It's free and it covers a lot of your questions in great detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply