Hot answers tagged

18

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 ...


14

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 ...


9

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 ...


7

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 ...


6

I found the issue myself. I will document it in case anyone else is facing the issue in future. The following user definition: CREATE USER [Angela] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [Angela]; created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist: ...


5

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

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 ...


5

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).


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 ...


4

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


4

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/


4

You can use the AgileSqlClub SqlPackage Deployment Filter. Brief instructions reproduced from the original article by Ed Elliott: Download the filter from agilesqlclub.codeplex.com Put the DLL into the same folder as sqlpackage.exe Add these command line parameters to your deployment: ...


4

The checkbox applies to each option "positively" so that e.g. ticking "script database collation" will generate the appropriate statements to alter collation settings. If not ticked, the deployment will ignore those differences by not scripting the changes.


3

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


3

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) ...


3

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


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

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

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code: USE [tempdb]; GO PRINT DB_NAME(); IF (USER_ID(N'g') IS NULL) BEGIN PRINT 'Creating user [g]...'; CREATE USER [g] WITHOUT LOGIN; END; ELSE BEGIN PRINT 'Dropping user [g]...'; DROP USER [g]; END; It builds ...


3

You are correct, the only way to "rearrange" the order of the columns is to create a table with the new structure and push the old data into it, drop the old table and then rename the new table (or some variation of that). It requires copying all the data in the table and some drops and renames.


2

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


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

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 ...


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

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 ...


2

First, I would go with an existing solution, such as Liquibase. If you use an ORM such as Hibernate, it can also do schema migrations for you. Otherwise, it would go something like this: Start with an initial database schema script, and give it a version number, such as 0.0.0001.sql. It would contain mostly CREATE statements to create tables, etc, such as: ...


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

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. ...



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