I do not believe the version of SQL I am connecting to has anything to do with the problem. I have now conducted this test on the following versions of SQL Server:Īnd gotten the same results in all cases. :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\" I go through the exact same steps using VS 2008 and I get this script: : setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\" : setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\" I run the comparison and it generates this script:ĪNSI_PADDING, ANSI_WARNINGS, ARITHABORT , I go to the options window and check every ignore box, except for Schemas and Tables. I select Testing 1 as my source and Testing 2 as my destination. I connect to an instance of SQL server and run this script: This is the process I go through to test/replicate this bug… If I am using schema compare in VS 2008, and I check the option to ignore indexes, the compare script ignores the indexes and reports the tables as equal. If I am using schema compare in VS 2010, and I check the option to ignore indexes, the compare script still tries to create/drop the index. I can replicate the issue by using this script to generate some databases:ĭepending on which is source and which is target, the schema compare always either wants to create or drop the index, regardless of the status of the “Ignore Indexes” I am connecting to SQL 2008 databases for both my source and target. I am using VS 2010 Ultimate SP1, running on Win 7 Enterprise 圆4. I did not have this problem in the VS 2008 tool. I have gone so far as to ignore everything except for the Schema and Tables and I still get a long list of tables where the only differences are indexes. The option to Ignore Indexes does not appear to have any effect on the schema compare results. Another post to follow soon will be on “Webjob creation via Azure DevOps”.I have just started trying to use the schema compare tool in VS 2010 and have a run into what looks like a bug. This brings us to the end of this post on SSDT and Azure DevOps. Avoid making changes manually to the server database.Create post/pre deployment scripts if you wish to insert, update data on the Azure SQL database, but know that scripts will run every time.Dacpac implementation only handles the schema level changes.For example, any object creation statements must be executed inside object existence checks. Therefore, they must be re-runnable and idempotent, as they are executed as part of every deployment. An important fact to take note of is the pre and post-deployment scripts added to the Azure DevOps pipeline will run every time in the context of the database deployed to, regardless of environment.Sample drop statement for dropping index in a pre-deployment script:ĭROP INDEX IF EXISTS ON. To solve this, drop statements can be defined in a pre-deployment script to drop the already created indexes and then Dacpac will create them again without giving error. This was because the indexes had been already added to the database manually. Once while deploying the Database changes via Azure DevOps, the pipeline failed with the error “index already exists”.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |