search

microsoft / sqlmanagementobjects

Sql Management Objects, an API for scripting and managing SQL Server and Azure SQL Database
Other
132 stars 22 forks source link

SMO, CREATE DATABASE: doesn't always replicate file/filegroups accurately. #144

Open sdonovanuk opened 1 year ago

sdonovanuk commented 1 year ago

Imagine a database on server A, with several files: PRIMARY DATA, LOG, and additional DATA files belonging to a filegroup. Now imagine you have server B. You want to swap the DATA/LOG drives from server A, to server B, for example, to do a "quick" OS/SQL upgrade (obviously, in a virtual/cloud environment). So: you prepare a new server, flip the drives, and voila.

Before moving the volumes you need to pre-create the database on server B, such that when you flip volumes and boot the SQL Server B, the database is known, and goes ONLINE (yes, I am aware of other db-scoped attributes, login mappings, etc.), but bear with me. :-)

Then, you use SMO/Database to script the database create. It builds the CREATE DATABASE and adds all of the necessary files.

Then, you swap the volumes, boot server B -- and sometimes . . . . you get an error, something like:

2023-05-25 10:30:18.060 spid27s An unexpected file id was encountered. File id 3 was expected but 7 was read from "E:\MSSQL\DATA\blah_5_new.mdf". Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.

What we found, is that SMO isn't "accurately" building the CREATE DATABASE statement. It adds all files into one statement. That is incorrect (in this case, at least). What it should be doing, is building CREATE DATABASE for the primary DATA and LOG files, and then using a combination of ALTER DATABASE {database} ADD FILE (or REMOVE FILE), to match the fileId values, and more importantly to introduce required "gaps" into the fileId values.

We wrote such code to solve this problem. Was wondering if SMO should be "fixed" too, if deemed a bug? Admittedly, if you're just moving random databases between machines this logic is necessary, but if you're moving ALL databases on a machine, then it comes into play.

Thanks!

shueybubbles commented 1 year ago

thx for reporting an issue. I think script generation was designed solely with 1 database in mind, not a collection of databases. You could let SMO do all the work and use Transfer, where you can provide DatabaseFileMappings to tell it where on the destination to put the files.

Doesn't the logic to calculate the order of the statements depend on knowing that the destination is a clean server? IE there's no deterministic way to set a file id from the TSQL; you are depending on side effects/implementation details of SQL Server to set them, correct?