March 1, 2005 at 7:17 pm
Needed: a tool to allow non-DBAs to do production database updates safely without installing the sql client tools or adding them to the sysadmin group.
Our company is fairly new to SQL Server so roles are evolving. I am "acting" as DBA but primarily for backup and disaster recovery planning. Perhaps 10% of our client data has been migrated from the legacy FoxPro application to the new SQL Server .Net application.
For code AND database update releases to production in the SQL environment, a configuration manager has been hired. The level of sql knowledge there is still an unknown, to me. The senior developer/architects for the new application supervise the running ( by the config manager ) of database update scripts that have been through the QA process.
The CM manager has been added to the SQL production sysadmin group by the architect so I'm looking for a tool that will allow such updates to be selected and run without the need to install query analyzer and give such rights to the CM. More generally, what do you think of this arrangement? I had never heard the term configuration manager prior to 12 months ago.
Randy
March 2, 2005 at 1:01 pm
If by updates you mean changing data, the CM does not and probably should not have sysadmin rights. db_Datareader and db_Datawriter are probably more than sufficient.
As for a tool to effect those changes, that's generally what an application "front end" is for. But assuming there isn't one, you need to have QA (I wouldn't recommend EM for this kind of thing.) Maybe a better choice would be to use Access to build a simple application front end.
On the other hand, if you mean that the CM will make changes to the data structures (tables, views, stored procs, etc...) then he needs db_ddladmin or possibly db_owner group membership.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 2, 2005 at 5:06 pm
Yes, the way it's working right now, database updates ( whether updates,inserts or schema changes ) are QA'd then run in query analyzer by the configuration manager. First this person sets the database in single_user mode in Enterprise Manager but I've let them know that can be scripted, probably more safely since one of the right_click options when you use EM is "DELETE" !!!
Right now I'm looking for the right syntax to set a database in single_user mode, then VERIFY that it actually happened, before running the update and then resetting the status. Don't have it quite yet.
alter database statustest set SINGLE_USER with ROLLBACK in 20 seconds
go
declare @returnvalue int
@returnvalue=exec master.dbo.sp_dboption 'statustest','single user'
if @returnvalue=1
end
set MULTI_USER with NO_WAIT
go
March 3, 2005 at 8:38 am
Just wrap it in a transaction and include the appropriate error checking.
BEGIN TRAN
ALTER DATABASE statustest SET SINGLE_USER
WITH ROLLBACK IN 20 SECONDS
IF @@ERROR <> 0 GOTO ErrBlock
do updates....
IF @@ERROR <> 0 GOTO ErrBlock
ALTER DATABASE statustest SET MULTI_USER
COMMIT
RETURN
ErrBlock:
ROLLBACK
RETURN
That should about do it...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply