Red Gate forums :: View topic - Extended Properties and Developer Update Access
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Doc 2
SQL Doc 2 forum

Extended Properties and Developer Update Access

Search in SQL Doc 2 forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
dbaRobRich



Joined: 15 Jun 2010
Posts: 5
Location: Kansas City, MO

PostPosted: Tue Jun 15, 2010 5:10 pm    Post subject: Extended Properties and Developer Update Access Reply with quote

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?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Jun 15, 2010 5:27 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
dbaRobRich



Joined: 15 Jun 2010
Posts: 5
Location: Kansas City, MO

PostPosted: Tue Jun 15, 2010 7:04 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Jun 15, 2010 9:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
dbaRobRich



Joined: 15 Jun 2010
Posts: 5
Location: Kansas City, MO

PostPosted: Tue Jun 15, 2010 10:05 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Jun 15, 2010 10:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
dbaRobRich



Joined: 15 Jun 2010
Posts: 5
Location: Kansas City, MO

PostPosted: Tue Jun 15, 2010 10:14 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Jun 15, 2010 10:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
dbaRobRich



Joined: 15 Jun 2010
Posts: 5
Location: Kansas City, MO

PostPosted: Thu Jun 17, 2010 3:48 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
rein08



Joined: 11 Jul 2010
Posts: 3

PostPosted: Tue Jul 13, 2010 11:11 am    Post subject: Reply with quote

putting datas on a server to another is made easy nowadays.
Smile
You should be checking out new products out in the market.
HAve a nice day!
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group