Automation database deployment with refactored objects
While automating the deployment for my SQL Server Data Tools (SSDT) projects, as mentioned in a previous post: Automating SQL Server Database Projects Deployment, I found it important to create / maintain the projects refactor logs. This is because SSDT / DAC framework uses the model driven deployment method, which works by comparing the model with the target database to create / execute a delta script for updating the database to match the model. A problem with is method is miss identifying objects which have been renamed or transferred to a new schema.
For code objects like: store procedures, functions, views etc. this would be more of a nuisance as the database will contain redundant code. This would happen if the deployment option DropObjectsNotInSource is not set to true. This would make it harder to maintain the database and may cause future deployments to fail if schema binding is involved.
However for tables or columns, again depending on deployment options, this will cause one or more of the following: data loss; deployment failures or unnecessary steps within the delta script. A group of the unnecessary steps that would be generated, mainly when columns are renamed, are table rebuilds. These steps could cause deployment failure when copying the data from original table to the temp table if the renamed column is without a default value and doesn’t allow nulls. When the option BlockOnPossibleDataLoss is set to false which allows the framework to drop columns and tables regardless if there is data. Could cause unintentional data lost if drop column / table were actual renamed / transferred because the data wouldn’t be copied to new the column / table.
The SSDT mechanism for managing renames / transfer of database objects is by having the actions recorded in a log file call: <ProjectName>.refactorlog. The refactor log is then used within the deployment to help generate the required steps to update the database.
To create / update the refactor log is by using the SSDT refactoring options by following these steps:
One annoying problem, this is dependent on the object being renamed / transfered, is that the refactor options will not rename the file within the project. So you could end up with a file called one thing ie: Myschema.csp_MyStoredProc.sql but the object inside could be MyTransfer.csp_MyRenamedProc. This can make the solution/project view impossible to use to find objects.
There are also two other options available which are mainly aimed at code objects like views, stored procedures and functions etc. I have not use these functions so I don't know if they impact the refactor log.
further reading:
For code objects like: store procedures, functions, views etc. this would be more of a nuisance as the database will contain redundant code. This would happen if the deployment option DropObjectsNotInSource is not set to true. This would make it harder to maintain the database and may cause future deployments to fail if schema binding is involved.
However for tables or columns, again depending on deployment options, this will cause one or more of the following: data loss; deployment failures or unnecessary steps within the delta script. A group of the unnecessary steps that would be generated, mainly when columns are renamed, are table rebuilds. These steps could cause deployment failure when copying the data from original table to the temp table if the renamed column is without a default value and doesn’t allow nulls. When the option BlockOnPossibleDataLoss is set to false which allows the framework to drop columns and tables regardless if there is data. Could cause unintentional data lost if drop column / table were actual renamed / transferred because the data wouldn’t be copied to new the column / table.
The SSDT mechanism for managing renames / transfer of database objects is by having the actions recorded in a log file call: <ProjectName>.refactorlog. The refactor log is then used within the deployment to help generate the required steps to update the database.
To create / update the refactor log is by using the SSDT refactoring options by following these steps:
- Select the object you wish to re-factor in the SQL Server Object Explorer under projects.
- Right mouse click on the object and hover over the refactor menu item and select the required action:
- Rename: The rename option will record that an object has been renamed. This will allow the deployment to create the following statement: EXEC sp_rename '<OldNameOfObject>', '<NewNameOfObject>';
- Move To Schema: The move schema refactor option will record that an object belonging to one schema will be moved to the new schema. This would cause the following statement to be created in deployment: ALTER SCHEMA <NewSchema> TRANSFER <ObjectType>::<OldShema>.<NameOfObject>;
One annoying problem, this is dependent on the object being renamed / transfered, is that the refactor options will not rename the file within the project. So you could end up with a file called one thing ie: Myschema.csp_MyStoredProc.sql but the object inside could be MyTransfer.csp_MyRenamedProc. This can make the solution/project view impossible to use to find objects.
There are also two other options available which are mainly aimed at code objects like views, stored procedures and functions etc. I have not use these functions so I don't know if they impact the refactor log.
- Expand Wildcards: The expand wild cards refactoring is there to help remove select * statements by replacing them with all the column names.
- Full-Qualify NamesThe full qualify refactoring option is about updating the column names with a qualified table name or alias.
further reading:
Comments