I am using Visual Studio version 2022 to develop and maintain an application database.
The database has over 1900 tables,1600 stored procedures, 1600 views, 200 triggers, 120 functions…
It takes approximately 45 to 60+ minutes to do schema compare.
Is there a way to accelerate the schema compare or to partition the database project so that it more manageable when running schema compare?
I have tried:
- Running Visual Studio on a beefy virtual desktop
- Run a trace to create plan guides which did help for the first schema compare after creating the plan guides and then it reverted back to it’s usual 45+ minutes to complete
- Turning ON/OFF “USE HARDWARE GRAPHICS ACCELERATION”
It’s still taking too long.
MindDData is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
I think I’ve resolved the Visual Studio Schema Compare taking too long to complete.
I have a virtual desktop, lets call it “V_Desktop”, which has 16 GB RAM and 2.4 GHz 2 processors 3 cores each.
Installed SQL Server Developer edition on V_Desktop, configured SQL Server to use max 8 GB memory, and then restored the source database on the local default instance, lets call the database “X_Db”.
From V_Desktop, I’ve then run Visual Studio, open my DB project, Built the solution which completed with errors and then run the Schema compare using “X_DB” as my source database and my DB within my project as the target.
Schema compare then completed in 8 minutes the first time and within 3 minutes the second time, that’s within 10 minutes… Wow!
So it looks like running SQL Server Developer edition where the source database is and running Visual Studio Schema compare on the same machine has resolved the problem.
I am not sure if Building the project would have made any difference but I’ve tested the schema compare twice now and it’s been consistently completing within 10 minutes.
MindDData is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.