Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Here are some some answers to your questions:</h2> <ol> <li>Should both test and production environments be built from source control? <strong>YES</strong> <ul> <li>Should both be built using automation - or should production by built by copying objects from a stable, finalized test environment?</li> <li><strong>Automation for both. Do NOT copy data between the environments</strong></li> <li>How do you deal with potential differences between test and production environments in deployment scripts?</li> <li><strong>Use templates, so that actually you would produce different set of scripts for each environment (ex. references to external systems, linked databases, etc)</strong></li> <li>How do you test that the deployment scripts will work as effectively against production as they do in test?</li> <li><strong>You test them on pre-production environment: test deployment on exact copy of production environment (database and potentially other systems)</strong></li> </ul></li> <li>What types of objects should be version controlled? <ul> <li>Just code (procedures, packages, triggers, java, etc)?</li> <li>Indexes?</li> <li>Constraints?</li> <li>Table Definitions?</li> <li>Table Change Scripts? (eg. ALTER scripts)</li> <li>Everything?</li> <li><strong>Everything, and:</strong> <ul> <li><strong>Do not forget static data (lookup lists etc), so you do not need to copy ANY data between environments</strong></li> <li><strong>Keep only current version of the database scripts (version controlled, of course), and</strong></li> <li><strong>Store ALTER scripts: 1 BIG script (or directory of scripts named liked 001_AlterXXX.sql, so that running them in natural sort order will upgrade from version A to B)</strong></li> </ul></li> </ul></li> <li>Which types of objects shouldn't be version controlled? <ul> <li>Sequences?</li> <li>Grants?</li> <li>User Accounts?</li> <li><strong>see 2. If your users/roles (or technical user names) are different between environments, you can still script them using templates (see 1.)</strong></li> </ul></li> <li>How should database objects be organized in your SCM repository? <ul> <li>How do you deal with one-time things like conversion scripts or ALTER scripts?</li> <li><strong>see 2.</strong></li> <li>How do you deal with retiring objects from the database?</li> <li><strong>deleted from DB, removed from source control trunk/tip</strong></li> <li>Who should be responsible for promoting objects from development to test level?</li> <li><strong>dev/test/release schedule</strong></li> <li>How do you coordinate changes from multiple developers?</li> <li><strong>try NOT to create a separate database for each developer. you use source-control, right? in this case developers change the database and check-in the scripts. to be completely safe, re-create the database from the scripts during nightly build</strong></li> <li>How do you deal with branching for database objects used by multiple systems?</li> <li><strong>tough one: try to avoid at all costs.</strong></li> </ul></li> <li>What exceptions, if any, can be reasonable made to this process? <ul> <li>Security issues?</li> <li><strong>do not store passwords for test/prod. you may allow it for dev, especially if you have automated daily/nightly DB rebuilds</strong></li> <li>Data with de-identification concerns?</li> <li>Scripts that can't be fully automated?</li> <li><strong>document and store with the release info/ALTER script</strong></li> </ul></li> <li>How can you make the process resilient and enforceable? <ul> <li>To developer error?</li> <li><strong>tested with daily build from scratch, and compare the results to the incremental upgrade (from version A to B using ALTER). compare both resulting schema and static data</strong> </li> <li>To unexpected environmental issues?</li> <li><strong>use version control and backups</strong></li> <li><strong>compare the PROD database schema to what you think it is, especially before deployment. SuperDuperCool DBA may have fixed a bug that was never in your ticket system :)</strong></li> <li>For disaster recovery?</li> </ul></li> <li>How do you convince decision makers that the benefits of DB-SCM truly justify the cost? <ul> <li>Anecdotal evidence?</li> <li>Industry research?</li> <li>Industry best-practice recommendations?</li> <li>Appeals to recognized authorities?</li> <li>Cost/Benefit analysis?</li> <li><strong>if developers and DBAs agree, you do not need to convince anyone, I think (Unless you need money to buy a software like a <a href="http://www.innovartis.co.uk/products/products.aspx" rel="nofollow noreferrer">dbGhost</a> for MSSQL)</strong></li> </ul></li> <li>Who should "own" database objects in this model? <ul> <li>Developers?</li> <li>DBAs?</li> <li>Data Analysts?</li> <li>More than one?</li> <li><strong>Usually DBAs approve the model (before check-in or after as part of code review). They definitely own performance related objects. But in general the team own it [and employer, of course :)]</strong></li> </ul></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow should you build your database from source control?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAndre Figueiredo
    1. USvan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow should you build your database from source control?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for your answer! Do you feel these recommendations apply to all projects? Do you know of any tools that help achieve this level of automation? I will be updating my question as more people weigh in it. Also, be aware my "teaser" questions were not meant as a definitive list of concerns to address - but more as a starting point for discussion.
      singulars
      1. PO
      1. USLBushkin
    2. COClear. I think you raised a very-very good question. And I really hope the question gets enough traction for you to compile a great HowTo wiki on the topic. ---- from the tools: I used dbGhost from Innovartis, which I mentioned in the answers for managing MSSQL server and it did a great job. There probably are other tools for the job, but given that full SQL schema is not really standard among vendors, there is no all-in-one solution (for all SCM and RDMBS).
      singulars
      1. PO
      1. USvan
    3. COGood answer. I'm assuming "look up lists" means the data that is used to populuate <select> boxes? That may not always be possible, since that data might be modified by users (in some way) on production. Keeping backups makes sense in this case. You also suggest recreating the database from scratch as part of a nightly build. I don't think thats a good idea; it can delete work in progress, or require re-installation/configuration of other software. Finally, I'd suggest creating test-modes, data validators, and other tools instead of building from scratch to ensure resilient processes.
      singulars
      1. PO
      1. USRichard Levasseur
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload