asked
DB upgrade
I am trying to figure out mechanisms for upgrading the DB. Presently i use SQL 2008.I have read that there are some tools (free as well paid) to check the difference of DB schemas. Also there is tablediff in SQL 2008. Now i was thinking of upgrading the DB through scripts with the installers. I will be maintaining the DB versions in the system as well. How do i use the information from the tool and upgrade the DB in a client system? I can give an use case: Suppose client has a DB schema version 1.2, I want to upgrade the schema to 1.5 . How do i do that? should be running the scripts of 1.3 and 1.4 as well? whats the better solution in this case.
There are a number of tools that compare schema between two databases. I work for Red Gate software, and we make one called [SQL Compare][1]. If you have Visual Studio 2012, Microsoft has a free one built into the SQL Server Database Tools (SSDT) which is OK. Either will work for what you're looking for. But to really get this under control, you need to be looking to place your database's structures into source control. That way you can version them very carefully, knowing exactly what is in 1.3,1.4,1.5, etc. If you do that, then you can either generate a change script to go from 1.3 to 1.5 all on your own, or you can stack up the change scripts from 1.3 to 1.4, to 1.5. It really depends on if you can know what version the client is going to or not. If you know, generate a single script between the appropriate versions. If you don't, create all the scripts you need and pass them on. Red Gate has an additional tool that can help you here too. It's called SQL Source Control. It allows you to use whatever source control system you want directly from within SQL Server Management Studio. But most of the work you'd need is covered by SQL Compare. I wrote a few chapters in the book [SQL Server Team Based Development][2]. The tools I use are a little older now (the book is from 2010), but the concepts are the same. You can download the book for free from here. [1]:
http://www.red-gate.com/products/sql-development/sql-compare/ [2]:
http://www.red-gate.com/community/books/index
Hi Grant, I have a query. Suppose the changed database Schema involves data to be also moved . In that case how do we do an upgrade? i mean whats the best practise ? Can we do it with the tools, because these tools generate script bases on the difference between the Schema. I will illustrate with an example. suppose present DB version is 1.2. In 1.3 i have added a new table and i have moved a bit of data from one of the tables to this. Now if i compare the schema , i would get diff in schema in a script. How would i automate the data movement through the tool? The major prob will be if i want to upgrade from 1.2 to 1.6 and 1.3 has the data changes along with the schema, how to handle these kind of situations?
Well, the SQL Source Control tool has a piece called Migrations. This identifies individual changes that might be changes that would result in data loss. You can then substitute a custom script for these changes. You'll be able to do the upgrades your referencing in that way.
12 People are following this question.
