Extended Properties and Developer Update Access

Documents SQL Server 2005 and 2008 databases.

Extended Properties and Developer Update Access

Postby dbaRobRich » Tue Jun 15, 2010 5:10 pm

In our environment, developers do not have any update permissions in our Production environments. One of our developers purchased SQL Doc to document our numerous databases. She has update permissions in the nonProduction environment and is using the extended properties to document the database and is various objects.

As the Production DBA, how do I move her extended properties from the nonProduction database to the Production database?
dbaRobRich
 
Posts: 5
Joined: Tue Jun 15, 2010 4:57 pm
Location: Kansas City, MO

Postby David Atkinson » Tue Jun 15, 2010 5:27 pm

You could try using SQL Compare to do this, although extended properties are included with each object, so if objects differ in your two databases, you'll have to be careful that you don't push unwanted changes to production along with the extended properties.

In an ideal world, how would you like for this to work?

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby dbaRobRich » Tue Jun 15, 2010 7:04 pm

A tool that would just move the extended properties would be an ideal solution. Maybe this could be a new feature of SQL Doc or SQL Compare.

I understand the benefits of storing the documentation information directly inro the subject database. However with our ever increasing seperation of production and nonproduction environments dictated by SOX auditors, it becomes very difficult, if not impossible, to grant the developers and designers any update permissions in the production databases.

Currently all updates are scripted and tested in nonproduction environments. Then the tested scripts are applied to the production envrionment by the production dba.

Thus we need an automated method of deploying this documentation information from the nonproduction environment to the production envrionment.
dbaRobRich
 
Posts: 5
Joined: Tue Jun 15, 2010 4:57 pm
Location: Kansas City, MO

Postby David Atkinson » Tue Jun 15, 2010 9:43 pm

Out of interest, how do you currently deploy other things, such as stored procedures? Couldn't you copy the extended properties across in the same way?

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby dbaRobRich » Tue Jun 15, 2010 10:05 pm

We use scripts generated by the developers to deploy all changes. Stored procedures are externally stored as drop/create scripts in our source library. Changes to database objects such as tables or indexes are scripted either by writing the script or using Management Studio to generate the script via the interactive interface.

I attempted to write a script to pull extended properties from one server and update to another server. The complication arrises because Microsoft provided system stored procedures are used to maintain the extended properties. To use these procedures requires dynamic SQL statements be generated by reading the information from the system tables and generating execute stored procedure statements. This is managable with one or two tables, but not with hundreds of objects in a database.

Another alternative is to update the system tables directly without using the supplied system stored procedures. My experience as a DBA leads me to believe that updating internal system tables directly without using provided stored procedures is not a best practice.

This brings me to the conclusion that the best option is to code a program to migrate the data from a development server to a production server. I was hoping this type of program could be provided by a vendor such as Red Gate instead of developing a program in house.
dbaRobRich
 
Posts: 5
Joined: Tue Jun 15, 2010 4:57 pm
Location: Kansas City, MO

Postby David Atkinson » Tue Jun 15, 2010 10:09 pm

Thanks for the further detail. I think you're right, this is something we ought to be doing. Out of interest, have you tried creating the SQL Compare script as I suggested? I'd be curious to know if that methodology for generating the upgrade script would work for you.

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby dbaRobRich » Tue Jun 15, 2010 10:14 pm

I haven't had time to try your suggestion. I will give it a try and post the results in a reply.

Thank you for your assistance. I appreciate you taking the time to understand our requirements.
dbaRobRich
 
Posts: 5
Joined: Tue Jun 15, 2010 4:57 pm
Location: Kansas City, MO

Postby David Atkinson » Tue Jun 15, 2010 10:20 pm

I'd appreciate you trying that out. If you don't own a license, you can download the tool and use it during its trial period. Let us know if you need a trial extension by emailing sales@red-gate.com

If you're dealing with a production database, please make sure you generate a script (rather than letting the tool apply the changes for you), and be very careful about checking to see what's in there so you don't accidentally push a change that isn't an extended property to your production database!

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby dbaRobRich » Thu Jun 17, 2010 3:48 pm

I used the SQL Compare tool to generate a script to move the comments in the extended properties from one environment into another. The SQL Compare tool correctly generated the script to move the information. There were some other differences between the two environments which were also included in the generated script.
Therefore I would like to see the SQL Compare tool enhanced to select only the comments. Until this enhancement is made, I can edit the generated script and discard the other changes I do not want to migrate at the same time.
Again, thank you for helping me find a solution to this problem.
dbaRobRich
 
Posts: 5
Joined: Tue Jun 15, 2010 4:57 pm
Location: Kansas City, MO

Postby rein08 » Tue Jul 13, 2010 11:11 am

putting datas on a server to another is made easy nowadays.
:)
You should be checking out new products out in the market.
HAve a nice day!
rein08
 
Posts: 3
Joined: Sun Jul 11, 2010 5:27 am


Return to SQL Doc 2

Who is online

Users browsing this forum: No registered users and 0 guests