search

dataplat / dbaclone

👯 Clone SQL Server databases using PowerShell previously named PSDatabaseClone
https://github.com/sqlcollaborative/dbaclone/wiki
MIT License
133 stars 29 forks source link

Store SQL version in Image table #136

Open DatKyle opened 3 years ago

DatKyle commented 3 years ago

Is your feature request related to a problem? Please describe. We have to support SQL 2012 up to 2019. unfortunately, backups aren't backwards compatible so if we generate an Image using SQL 2019 we can't create a Clone of that Image on SQL 2012. The issue we face is due to users trying to create clones for the wrong version of SQL.

Currently, if you tried the above it would create a Clone VHD, and attempt to mount the database, but fail with the below error: WARNING: [17:50:02][Mount-DbaDatabase] Failure | The database 'SQL2019Db' cannot be opened because it is version 904. This server supports version 852 and earlier. A downgrade path is not supported. This still creates a clone that has no database.

Describe the solution you'd like The solution would be to modify the New-DcnImage command to store the SQL version which generated the image when creating the image record. This would allow users to check the SQL version before cloning. We would also modify the New-DcnClone command to check if the user is creating a clone against the same or newer SQL version. this prevents a clone from being made if the SQL instance version doesn't support the version required.

This solution would return the SQL version in the Get-DcnImage command. It would be nice if we could pass in a SQL version into Get-DcnImage and Remove-DcnImage commands, which only returns/removes images with the same version. It may also be an idea to provide a flag to say whether the version can be lower.

Describe alternatives you've considered I did consider just modifying the New-DcnClone command to check that the SQL instance version is the same or newer than the image, but this would require a clone to be made before checking the version are compatible.

Latest version of dbaclone as of writing 0.10.45

sanderstad commented 3 years ago

That is a good case, saving the version and doing a check on that is a good idea. Thank you for that.

sanderstad commented 3 years ago

How do you see the version of SQL Server represented in the image information. There is the major version like 12, 13, 14, etc. Or we can use the the version like 2014, 2017, and 2019.

DatKyle commented 3 years ago

I think the Year version (2014, 2017, 2019) is best as it is more user friendly. If a user ran Get-DcnImage they could see that an image requires SQL 2017, whereas I don't think people (including me) would know major version 14 is SQL 2017.

With that said we could store the Major version and convert it to the Year version. I think this way would make it easier to compare the clone SQL version with the image as I don't see a way to get the Year version. If we include a SqlVersion flag this should accept both Major and Year versions, and if passed in a year it will convert it to the Major.