How to change xsd schema in a massive SQL Server database without a massive outage

230 Views Asked by At

I undertook the task of adding a feature to a web application, and the feature I added required modifying the xsd document that defines an XML schema. I added some new optional elements, etc.

The new schema is backwards compatible with the old schema, meaning that everything that is in the DB now under the old schema would be valid under the new schema (the new things are optional).

We deployed this to some small environments using a DACPAC style deployment, and there were no issues.

Now, we are trying to deploy against a prodlike dataset, and it is being extremely problematic. The dacpac is taking an extremely large amount of time. Like days - I need to get this down to an hour or two.

Does anyone know if there is a way to work around this type of issue?

If I know that the schemas are compatible, is there a way to just 'tell' ssms this, so that it can edit the schema without checking everything?

Failing this, is there a way to convert a typed XML column to an untyped XML column without massive validation? To clarify, I cant just do this:

Alter Table [Schema].[Table]
alter column [XmlCol] xml
;

because I get this:

Msg 5074, Level 16, State 1, Line 4
The object 'DF_Table_XmlCol' is dependent on column 'XmlCol'.
Msg 5074, Level 16, State 1, Line 4
The index 'IX_Table_OtherId_SomeFlag' is dependent on column 'XmlCol'.
Msg 4922, Level 16, State 9, Line 4
ALTER TABLE ALTER COLUMN XmlCol failed because one or more objects access this column.

I have been told, that even if I DO drop those constraints, SSMS will still take forever to change a typed XML to an untyped XML column...

Any other ideas?

EDIT: Seems that dropping the index, turning the typed XML into an untyped XML, and then adding the index back in was faster than expected - it took about a minute. When the DACPAC made a similar change, it took several hours for a single table. I plan on testing out the change from untyped to typed as soon as I can, but I'm still curious as to how other people have dealt with such issues... other than not using XML in a database, which would be totally rad if this was greenfield.

0

There are 0 best solutions below