Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating Database Objects


Migrating Database Objects

Author
Message
jeffrey yao
jeffrey yao
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jyao/migratingdatabaseobjects.asp



mark baekdal-145375
mark baekdal-145375
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 6

You make your life so hard!

To me everything starts and ends with source control. The developers develop it, I audit it and deliver it. Continuous code builds ensure that the developers don't check in code that breaks existing code. Code deployments are tested on copies of the target ensuring the script is faultless and all of this is automated as I use DB Ghost. Fair enough (to any sceptics) that I built the thing - but I know it works and so does my growing customer base like Dell, AGFirst, Lloyds etc.

And it complies with rule number one and two.


regards,
Mark Baekdal
http://www.dbghost.com

http://www.innovartis.co.uk

+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

 

 

 


Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

I agree - with DB Ghost handling the building of our databases and also the compare and upgrade as well Rules 1 and 2 are completely adhered to - Rule 2 because we are fully utilizing the power of our source control system to realize the benefits of our developers teamwork.

They don't waste any time using this method and neither do I thus leaving more time to get an even greater understanding of SQL Server - a real win-win!



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
AJ Ahrens
AJ Ahrens
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 Visits: 9

We use Harvest as our source control.  Our approach is as follows:

  1. Developers develop code and place it in Harvest (Dev env.)
  2. Code is migrated to Test and tested by QA team.  They also promote the code to Test env. in Harvest.
  3. Developers build install BAT files that install into Dev|Test|Staging|Production env.  These are placed in Harvest and migrated the same as code.
  4. After the install scripts are tested by Dev|Team members the code and install BATs are promoted to Staging (BTW install BATs use osql and produce error logs).
  5. DBA examines the scripts/bat files for anything incorrect.  They then run the BAT file and report errors.
  6. QA\UAT\performance testing is done in Staging with DBA assistance, Profiler, etc..
  7. Code is promoted to Prod and DBA examines the BAT file and runs it, reports errors.

The process for DBA to install code (minus backups CYA) is about 15 - 30 minutes.  All code migrations are done this way and it proves to be quite handy.





Good Hunting!

AJ Ahrens


webmaster@kritter.net
jeffrey yao
jeffrey yao
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868

Hi everyone,

All excellent comments. I once used both Harvest and VSS, and I agree with you all the source control is absolutely the right way to solve most of the problems. But on the other hand, I also worked for some companies / government agencies, who have "strange rules" that only load the source codes / products into VSS / Harvest once those codes have passed the UAT and move into production. I complained but to no avail.

On the other hand, to persuade some CTOs to budget for third party tools is morre difficult than you can imagine. Personally, I always use what are already inside SQL Server to better sell my 'cheaper' solutions.

But seriously, I find my approach has a much better advantage in terms of doucmenting purpose. In real world, each sheet file is dedicated for one product/database migration objects list. It is really simple and straight-forward compared with finding out database objects through source codes with thousands of lines.

I welcome more comments.





mark baekdal-145375
mark baekdal-145375
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 6

there's a saying that goes "why code when you can download"

Re-inventing the wheel is more costly than buying a well tested product that solves the problem.

But seriously, I find my approach has a much better advantage in terms of doucmenting purpose. In real world, each sheet file is dedicated for one product/database migration objects list. It is really simple and straight-forward compared with finding out database objects through source codes with thousands of lines.

DB Ghost does this - you don't have to, that's the point.

 

 


regards,
Mark Baekdal
http://www.dbghost.com

http://www.innovartis.co.uk

+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

 

 

 


jeffrey yao
jeffrey yao
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868

Hmmm, it seems I need to try the dbghost next time since so many people recommend it...  but isn't it too "eye-stressing" if you have to pickup various objects through GUI interface from thousands of different objects? and is there any team collaboration work with dbghost? (Excuse me if my questions are naive as I never use dbghost before) 

As said before, my current philosophy is to use what is available and do what I can do to benefit both my employer and my own career. To me, my approach is simple and useful, once my "migration engine" is done, I never have to change it unless I need to add new protocols.

But thanks Mark for ur comment, I will play with dbghost in the weekend and see what advantages I can get. But I have a feeling if I add a simple GUI interface to my "migration engine", I may make this a simple and flexible tool.





Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

DB Ghost is best used to allow easy collaboration between developers during the development phase and easy reporting and propagation of changes directly from any source control system.

For the developers it is easy to keep their dev databases in sync with source control and for DBAs it is easy to accumulate all of those changes (without having to keep any separate documentation up to date) by simply building and deploying what is in source control.  If any reporting of what schema or static data objects have changed is required you simply use the reporting and diff functions built in to every source control system.  For example you can run reports that give you 'all of the objects that changed between build X and build Y' or 'what objects have been changed as part of new feature X and by whom'.

It reduces the mundane workload for developers AND DBAs, simple as that.



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
jeffrey yao
jeffrey yao
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868

Based on your saying, it seems that we can take Better advantage of dbghost only if source control system exists. But in real world, this may not be true sometimes.

 

I’d like to raise the following questions / scenarios for your considerations:

  1. Can dbghost migrate some tables with some of the records, say for sales table, I need to migrate the sales table together with top 10 largest transactions in each year? For other tables, I may / may not need to populate them with specific business rules?
  2. How can I change the job schedule from original monthly running to weekly running during the migration?
  3. Can you show to your clients what you will do upfront with a simple and clear document so everyone can understand and make a comment? (I know dbghost can generate a report later, but to me I need the report first to get the review and approval and my client can even add their own business rule in the document, which I will use to generate my migration scripts directly, and I will not change a letter.)

 Yes, for 1 & 2, we can have additional scripts to achieve the purpose but that's not what I want.

Saying all these, I think maybe dbghost does not have a similar target function space as my approach tries to achieve. I’d say my approach has the limit as far as VBScript can reach (to interpret the self-defined protocols)





jeffrey yao
jeffrey yao
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868

Whether my view is economical or not is not important, the important thing is my approach is practical / flexible and helps me a lot in my daily work. I do not know much about your DBGhost but I may take a look sometime when I have time and discuss with you later. But if you really think my view is not important, let me tell you I am not the original creator but my clients are. To me, there is nothing uneconomical in the requirements from customers.

On the other hand, after reading your PDF document, I would say probably you do not understand my article fully. If you know how important it is to be able to customize a migration protocol to fit various scenarios / requirements, you may consider to make some improvements to your DBGhost in future.

BTW, I welcome constructive suggestions and discussions but do not expect to hear the words in the tone of teaching a pupil, such as

"Learn & try, a closed mind is a useful as a dead one in a ever expanding stage..."





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search