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


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


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

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


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.


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

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


3

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


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

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

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

Is there any way to prevent this time consuming process? There sure is! Stop caring about column order. If it is that important, be willing to pay the price, or spend more time up front on the design so that you're not re-deploying the same table multiple times before you get the column order "right." SSMS does the exact same thing if you use the ...


2

CSV files are not included in the dacpac that you deploy so you will need to make sure that you copy it with the dacpac and that if you use relative paths the working directory is what you think it is. What I do for extra files is set the "Build Action" of the file to "Copy if Newer", when the dacpac is built in the bin folder you will also have a "Data" ...


2

SQL Server Management Studio (SSMS) only supports a subset of the SQLCMD commands, and SQL Server Data Tools (SSDT) only supports a subset of the SQLCMD commands that are allowed in SSMS. The [:]!! SQLCMD command, while supported by SSMS, is not supported in SSDT Pre-Deployment or Post-Deployment SQL scripts. This is most likely due to the large amount of ...


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

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

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


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

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

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

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

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

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



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