Hello Everyone,
We have converted a existing web application application from Asp.net Web forms to Asp.net MVC.
The existing web-application hosted in dedicated server and the new one we are moving to Ms Azure cloud.
On the development server we have Microsoft SQL Server 2012 - 11.0.2100.60 and in Azure cloud environment we have Microsoft SQL Azure (RTM) - 12.0.2000.8.
In our development we have made some changes in existing tables (no of columns), procedures and datatype (columns) also we have created some new tables and procedures.
I just need that information that is there any tool or approach so that we can compare our existing tables , columns and datatype with the modified tables?
Or We have to do it manually by comparing one by one.
Kindly suggest
Thank you
March 16, 2021 at 11:51 am
This was removed by the editor as SPAM
If you need to compare the 2, you will either need to do it manually (bleh) or with a tool. Most tools that manage this are of the paid variety. For example, SQL Compare from RedGate is an awesome tool to do this but comes at a cost.
If you want to do it on the cheap, I would grab a copy of Visual Studio (has many uses and I think that the free version can handle SQL schema level compares), create a new SQL Server Database Project. Right-click on the project and select "Schema compare". Change the 2 databases you are comparing to the 2 you want to compare and click on COMPARE. It'll take some time (more objects means longer compare time), but once it is done, it will tell you the schema level differences.
Nice thing about the visual studio approach is that it will tell you what changed, what is new and what is removed. Due to it needing to scan a whole bunch of metadata, I would recommend running it during company downtime if possible. If not possible, I would recommend restoring a backup of LIVE to compare against to help reduce the impact on production. Last thing you want is a deadlock (shouldn't happen, but why risk it) or blocking (may occur, but should be very brief) that impacts production and you get a phone call because your query is running too long.
Now doing it manually is a pain in the butt, but if you only have a few objects to look at or only specific object types (stored procedures, tables, views, etc), it may be easier than trying to do it with a tool. I would still recommend grabbing a diff tool like winmerge though as it will save some time. Start up winmerge (or notepad if you are going to be eyeballing the differences) and SSMS. In SSMS, press F7 to bring up object explorer. Select the object type in SSMS (stored procedure, table, view, etc) and in object explorer you should get a nice list of objects. Select all of the ones you want to review and right-click Script As->Create to->New query window. Copy everything and paste it into winmerge (or notepad) on the left side. Repeat with the other database but put it on the right side of winmerge. Next, click on the "Compare" button in WinMerge and it will highlight all of the differences. Repeat for all object types you are wanting to validate.
My order of preference is SQL Compare (it is quick and easy approach, plus it can compare from backup files so you don't need to impact production)->Visual Studio (quick and easy but a bit more work to get things set up to compare if you want no impact to production)->winmerge (slow process, but easy enough for comparing specific objects)->notepad (error prone and really only useful if I am looking at a single object that I know changed that is short to read through. The longer the script, the more painful it is to compare).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 18, 2021 at 3:59 am
Thank you Mr. Gale for your reply on this.
I will go with winmerge.
March 18, 2021 at 12:08 pm
There is a way to do this using T-SQL. However, I much prefer using SQL Compare from Redgate (even though I do work for them now, I used the tool for 10 years before I started working there). It really be worth the investment over time. I used to have it open on my desktop every single day when I was a production & development DBA.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply