Correcting the database

bitman

Hello,

I have a few questions regarding my plan on correcting my database.  My database contains more than 900 tables for starter.

1.  I am using nvarchar as my PK and I wanted to change this to int.  How do I implement this the fastest way possible without go through each table one by one?

2.  Should I just add a new column as my PK or should I change the data type?

3.  Does the migration capture the changes so that I can deploy it to my customers?

4.  How do I deploy these migrations as part of my own application?

Kendra_Little

Hello,

1. SQL Server doesn't provide a simple way to do this type of refactoring. What you are doing is changing the structure of the data which is being stored for the business key column of a table, which is an essential part of the table structure. You will need to remove foreign key relationships / other constraints, remove primary keys, make the column change, and then recreate the PK, indexes, and constraints.

There are some scripts from around the web which may help you loop through tables and save some time on sites like StackOverflow. Needs tend to vary depend on the database schema you're changing, so it's likely you will need to customize the scripts to meet your situation. You don't have to worry about the individual scripts being perfect, though -- you just need to get your development database into the state you'd like it to reach in the end. So if you make a mistake, you can correct it.

2. This is going to vary based on the size of your tables, the nature/quality of the data you have in the columns, and the downtime you have available for deployment. There's no single answer to this. For very large tables, some might even choose to create a new table, insert the data into it, and do a switcheroo for performance reasons. (Here's an example of a very fancy approach that some use if they can't have downtime.)

3. Yes. When you have a set of changes made in a database that you would like to commit to version control, at that point you can generate migration scripts and import the changes, review them, and commit them.

The SQL Compare Engine will analyze the objects which have been changed that you've selected to import and compare this with the prior schema which you captured in version control. It will script out the most efficient way to to make the version in source control reach the desired state of your dev database. You can review that code and tweak it as required. 

4. Customers typically use some combination of orchestration systems such as Jenkins, Azure DevOps, Octopus Deploy, etc to automate builds, tests, and deployments of database code for SQL Change Automation. The functionality is also available in PowerShell scripts if you wish to execute the PowerShell directly.

Hope this helps,
Kendra

way0utwest

I agree with what Kendra has listed here. This isn't a simple thing to do, and not necessarily something you do all at once. There is risk here that can break things. I'd actually like #2, and work through a table(s) and it's relationships in stages, then repeat. For example, I might

1. Add an int key to the parent table and populate it in a deployment
2. add an int PK/FK to a dependent table. 
3. Populate a child new PK with the new values from the parent, using the old PKs to join tables

I'd repeat that for one table. Then start to slowly move through other tables.
If I'm sure a single table is done, I'd also start refactoring the app to use the new key.

As with complex changes in a database, I might stagger this across time, slowly working on the refactoring to ensure my app continues to work while disruptions and risk are minimized.

bitman

Thank you guys for chiming in.  I appreciate the feedback.  I will take your comments into account and plan accordingly.

With regards to #4, my app is a desktop application and during loading I check for any updates from our server.  I want to ask if there is a way to also deploy the migrations I will generate using SCA to my target database?  I mean, does the project I created in SCA sufficient to be an executable application for migration deployment?  Or I need to move the migration files inside my application and run it from there?  Sorry for a rookie question but if there is a solid solution already, I would rather use that instead of reinventing the wheel.

Thank you very much.

bitman

I hope to get some advice regarding my question on #4 please?  Thank you.

Kendra_Little

Hello,

Apologies for the delay, just catching up on this thread this morning. I believe we have shifted the discussion of question #4 to this thread now.

Cheers,
Kendra