Extended Properties and Developer Update Access

dbaRobRichdbaRobRich Posts: 6
edited July 13, 2010 6:11AM in SQL Doc Previous Versions
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?
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbaRobRichdbaRobRich Posts: 6
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbaRobRichdbaRobRich Posts: 6
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbaRobRichdbaRobRich Posts: 6
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbaRobRichdbaRobRich Posts: 6
    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.
    · Share on Twitter
  • rein08rein08 Posts: 3
    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!
    · Share on Twitter
Sign In or Register to comment.