November 25, 2016 at 4:01 am
Hello
We have an enterprise application on Visual Basic 6.0 that is currently running with SQL Server 2005.
The application is quite huge and there are almost 1000 forms + 1400 database tables. Within the code, there are SQL queries inline and depreciated SQL functions as well that were compatible with the SQL Server 2000/ 2005.
We want to upgrade our database to the latest SQL Server 2014 or 2016, but we dont want to put our efforts on the application upgrade side.
Could you guys help me that
1. Are there any third party database drivers using which we can connect VB6 (Old inline queries) to the SQL Server 2014/2016 database. (We will only upgrade the Database from 2005 to 2014)?
OR
2) Are there any tools available that can help us in upgrading the inline SQL old queries to the latest SQL Standard. These queries are written within the VB6 application. ?
Or any other approach that can help us?
Thanks in advance
Wajahat
November 25, 2016 at 7:30 am
You can start by running the Upgrade Advisor from Microsoft. Besides you can also run the profiler for a period of time that logs the "Deprecation Announcement" and the "Deprecation Final Support" events. Search the related code in the VB application (found by the advisor or the trace) and fix only those queries.
November 28, 2016 at 7:53 am
wajahatabbas (11/25/2016)
HelloWe have an enterprise application on Visual Basic 6.0 that is currently running with SQL Server 2005.
The application is quite huge and there are almost 1000 forms + 1400 database tables. Within the code, there are SQL queries inline and depreciated SQL functions as well that were compatible with the SQL Server 2000/ 2005.
We want to upgrade our database to the latest SQL Server 2014 or 2016, but we dont want to put our efforts on the application upgrade side.
Could you guys help me that
1. Are there any third party database drivers using which we can connect VB6 (Old inline queries) to the SQL Server 2014/2016 database. (We will only upgrade the Database from 2005 to 2014)?
OR
2) Are there any tools available that can help us in upgrading the inline SQL old queries to the latest SQL Standard. These queries are written within the VB6 application. ?
Or any other approach that can help us?
Thanks in advance
Wajahat
You could also back up the database, restore it onto the SQL 2014 instance, and leave it's compatibility-level setting alone, and take a copy of your application into a test environment and see what breaks. You'd still have to go through every possible form and screen to catch all the problems, but searching for deprecated code is potentially only half the problem, as in many cases, the code might have been designed around that deprecated functionality, and you may well need to completely re-factor it just to operate in the SQL 2014 box in SQL 2005 compatibility mode, to say nothing of using it in full SQL 2014 mode. This is unlikely to be easy or quick. The resolution of this has kinda waited too long, so the price to be paid in terms of effort to remediate is going to be higher. I don't see any easy way to avoid going through ALL the functionality, whether it's a search for deprecated code or a full functionality test.
EDIT: Part of the reason is that the "cardinality estimator" has changed with SQL 2014. That alone might cause a perfectly valid query that would run just fine in SQL 2005 to run rather poorly from a performance perspective when run in SQL 2014, so not everything that can go wrong can necessarily be found by just searching for deprecated code.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 30, 2016 at 9:35 am
wajahatabbas (11/25/2016)
We want to upgrade our database to the latest SQL Server 2014 or 2016, but we dont want to put our efforts on the application upgrade side.
How much longer do you expect a VB6 application to run on modern platforms?
If you only focus on the DB engine, you'll likely have to do refactoring every time a new OS releases. The band-aids you put in to connect to SQL2014 will make the code more complicated and expensive to maintain. It would be much better to invest in re-writing the application to .Net/SQL2016.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply