I have a DB that has combined keys as proimary keys and I have a task to rename one of the columns. What is the best way to do it witout having a down time (or with only some seconds of downtime)?
There are 10 different tables having the same type of primary keys: a combined key consist of key1 and key2 (example names, both varchar and it is a clustered index). I have a webserver on top of it and because of some technicalities I have to rename key1 to newKey1 and of cource it is prefered not to have any down times in the server.
I have the code in Database project and if I rename the field in code it will generate the script below that it will run on publishing:
EXECUTE sp_rename @objname = N'[dbo].[table1].[key1]', @newname = N'newkey1', @objtype = N'COLUMN';
-- Refactoring step to update target server with deployed transaction logs
IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'c27a5d55-e520-4e61-9586-3ca3d6e3dd57')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('c27a5d55-e520-4e61-9586-3ca3d6e3dd57')
Is it a good idea to update the DB by just spinning up the DB to an expensive plan (like 2000 eDTUs) and then just publish the code? Will it need to update all the clustered indexes? Anything important to check/remember before doing this?
Alternatively, I can do it in multiple steps:
1- Manually adding a new column newkey1 and have the default value set to key1
2- Manually changing the primary key to use the new key
3- Upload the code that doesn't have the old key and uses the new key
Is it better? does it have any risks compared to the 1st way? Which one is better and what to consider?