Tag Info

Hot answers tagged

14

You almost have your answer already: Create the new structure in parallel Start writing to both structures Migrate old data to the new structure Only write and read new structure Delete old columns As for step 3, use something like this (in one transaction): Insert what is not there yet: INSERT INTO new_tbl (old_id, data) SELECT old_id, data FROM ...


12

In my opinion, the key differentiator of true SOA systems (over the pseudo SOA, ntier/distributed systems that are becoming ubiquitous) is that there should be zero interaction between discrete services. Where this is achieved, any application you compose from these services can and should be built to tolerate the failure of any consistuent part. A failure ...


8

Everything is wrong with using backup files as deployment. But the burden is not on the DBA to provide the DDL, is on development. Your design and development artifacts should had been database installation and upgrade scripts. Never change anything in the database manually, everything should be modified using your app. Rails gets this in spades with he ...


6

No, there is nothing wrong with using a backup for initial deployment, in fact I would say that this is often the safest way to do it. There isn't really any "contamination" that can happen unless you have hard-coded things like server names or database names that are different in production than in the test environment. Though a backup / restore (much ...


5

Some objects are allowed to be created to facilitate Deferred Name Resolution. The assumption is that if you create a procedure that references dbo.MissingProcedure, you will create dbo.MissingProcedure some time between the moment you create the procedure, and the first time you execute it. Another situation that works is a table that doesn't exist yet. I ...


5

You may have run into a known issue with Visual Studio 2010: Connect request SO Question MSDN forum thread That said, Microsoft recommends you help Visual Studio detect the object dependencies it seems to be ignoring in its deployment script by: schema-qualify[ing] the names of objects that are involved in dependent relationships. So if everything ...


4

If the queries that are accessing the table that need to work while you're making this change are read-only, you could create a view that has the original column and uses the function in place of the computed column, then in a transaction rename the table and create a synonym with the original name that points at the view, then make your changes, start ...


3

You need at least two. Don't mess around in production.


3

The following approach has low risk: Make sure that all the modules accessing the table are well covered with automated tests. Create a new table. Alter all procedures that modify the old table, so that they modify both old and new tables. Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall ...


3

Are you referencing system objects, or objects within other databases?


3

Well I think that having a test system that supports the workload, data and edge cases you have in production should mitigate the need to roll back. If you can properly test the deployment in a similar system, you should have very few scenarios where you have to roll back. That said, you can also do many things to make your code changes backward compatible. ...


3

You might want to have a look at Liquibase - according to the Liquibase documentation, it can perform diffs (I'm not 100% sure whether this requires using Liquibase to version your schemas, though).


2

Even though you did not say it explicitly, I guess you removed the unnecessary objects from the restored database and now want to migrate the structure, and maybe some of the data, from the new (restored plus modifications) database to Production. If this is correct, then I would recommend Red Gate’s SQL Compare (Structure) and SQL Data Compare (Data, of ...


2

Requirement 2 requires the data be stored "normally": that is, you can't use encryption in the client for all data. Encrypting data in the client also contradicts requirement 1 Requirement 3 requires the media or the actual backups are encrypted. Encrypting the HDDs isn't reliable because once someone has the actual media then it can normally be unencrypted ...


2

Ended up finding an article that mentioned fixing this by running a repair on the Sql Server install. When this was run, the problem was solved.


2

Firstly, every environment is different. What works in my environment won't automatically work in yours. (We = DBA Team) In mine we have the following environments for this process Dev - Normally we restore a copy of live in preparation for a release and sanitise data as required. We then back that up and enable the dev to restore to that backup at will. ...


2

You need at least three servers in your scenario - Production, DR and Staging/UAT, all physically separate and sharing no infrastructure. Not even for basic services like DNS, LDAP, etc. Trust me on this. A good strategy is to replicate Prod to DR, then backup DR to tape (offloading the work of the backup from Prod) then use that tape backup to refresh ...


2

Try SchemaCrawler. It's open source and available on SourceForge. I've got some simple instructions to demonstrate it in my blog https://gilesey.wordpress.com/2012/11/15/a-lightweight-schema-diff-or-dump-from-oracle/


2

I say don't do it. Use an SQL script made from as standard as possible statements as you can make, taking note of what SQL server versions that version of the script worked for. I've had problems with software that was distributed via a backup when I've needed to do a re-install of the software at a much later date. Due to the age of the original backup ...


2

I think the answer to this in both yes and no. You said: This is a greenfield deployment so there is no existing data to be migrated. If this is an initial deployment then I see no issue with taking a backup of a development database to use in production. Saying that, you (or hopefully your DBA) would obviously have to cleanse the database and remove ...


1

We are facing the same problem - DACPAC does not support replication (as of now). What we do is - if the deployment involves/affects replicated tables then manually drop replication. Script out drop and create replication using GUI or Powershell Drop the replication Run your DACPAC to upgrade your system Create replication (scripted out previously) ...


1

Disclaimer: I work for Red Gate. Another tool that you may want to consider is Red Gate's Schema Compare for Oracle, which you can use to see a side-by-side view of schema differences and generate upgrade scripts. You can also carry out these tasks via the command line. It's worth noting that Schema Compare for Oracle has been designed specifically for ...


1

Not open source, but free to use is Oracle SQL Developer. At least since version 3.2 Oracle SQL developer contains tools to export a DDL (Database Export) and also to compare databases (Database Diff). Latest version can be downloaded here: Oracle SQL Developer


1

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that: Part 1: get the last version from the build server and deploy the file to generate the last working version of XMLA; take the generated XMLA from the build server and copy it to the QA server; Part 2: Now, on the QA ...


1

Deploying the stuff you need to move a job is pretty much just like packaging the stuff you need to move application code assuming that you correcly put all the new items and changes in source control. This includes your SSIS package, any object creation or alteration scripts such as tables, views, UDFs, stored procs, CLRS (Never ever create database objects ...


1

General rules for deployment would have kinks worked out when deploying to a TEST, QA or DEV system so there are no issues with the PROD deployment. If you are already running a cluster you can add an instance to the cluster at no licensing cost and use that as your TEST environment to iron out the deployment process. If the deployment to TEST goes south ...


1

From inside Microsoft SQL Server Management Studio, just right click on the database choose Tasks | Generate Scripts


1

Make sure that open_cursors is sufficient. What is the current value for open_cursors and session_cached_cursors? They should be high, start with 500.



Only top voted, non community-wiki answers of a minimum length are eligible