X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Upgrade From Sqlserver2008r2 To 2016, SQL Server 2012    
post Jan 23 2020, 04:24 AM

Posts: 598
Joined: 2-September 03
From: Galway, Ireland


I am working with a large db application of about 25 users and 75 tables, with an Access 2003/2007/2016 frontend and an sqlServer2008R2 backend,
so with the end of support for 2008R2 in January we are going to upgrade to sqlServer2016,
I am planning to upgrade all users to 2016 frontend afterwards (Even though I was glad that most of them where on 2007/2003 with the bad update in November).
Now my IT people reliably inform that there is no direct path from 2008 to 20016, we have to go from 2008 to 2012 then to 2016, so I will take them at there word for this one.
Now my concern/question is what are the/if any pitfalls when moving with an Access frontend from 2008 to 2016.
Now my experience has been when migrating from an Access db backend to sqlServer there has always been issues in different places that needed to be fixed,
like the re-design of a query because it ran slowly or didn't run at all due to some small quirky things that just don't work with sqlServer like the reference to a text box in a query on
a form runs fine on Accesss but starts to run very slow with an sqlServer db once it goes over 50,000 records.
Does anybody know of any issues that might arise with sqlServer2016.

Thanks in Advance for any helpfull suggestions.

Go to the top of the page
post Jan 23 2020, 07:23 AM

UtterAccess Administrator
Posts: 10,441
Joined: 7-December 09
From: St. Augustine, FL


In general terms, I wouldn't expect any particular issues. There may be a few things that need to be smoothed over (ODBC updates, maybe tweaking a data type mapping, switching a permission and things of that nature), but I've not done one of these in long enough to be able to list off specifics.

That said, big issues (such as performance when going from Access to SQL requiring re-writing of queries, etc) should be pretty much non-existent in upgrading SQL versions.

Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
post Jan 23 2020, 07:44 AM

Posts: 598
Joined: 2-September 03
From: Galway, Ireland

Hi Jack,

that makes good sense on both counts, it is nice to get some extra feedback on things before we proceed.

Thanks for your helpful advise.

Go to the top of the page
post Jan 23 2020, 07:57 AM

UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA

I agree with Jack. I haven't seen any issues with moving from 2008 R2 to newer versions of SQL Server and SQL Azure.

Rather, you might find feature changes that you may or may not be in a position to exploit right away. I did a quick Bingoogle search and turned up a lot of links, but mostly nothing significant showed up.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Jan 23 2020, 06:17 PM

UtterAccess VIP
Posts: 2,954
Joined: 12-April 07
From: Edmonton, Alberta Canada

You should be fine.

I was not aware that a 2008 SQL database needed anything at all. It should restore right into 2019 without issues.

It is possible that you can't "attach" the older 2008 files. So, I would simply recommend a backup be made in 2008 and then restore the database to 2019. Quite sure no jumps from 2012 or in-between are required.

So, it may well be possible that the existing database file (.mdf) can't be just attached. Remember, SQL server just opens and reads + uses a .mdf file very much like how JET/ACE is able to open a mdb or accDB file. From SQL servers point of view, its much like Access - it opens a database file.

I never had any issues with such a jump to a newer version of SQL server. Most often the issue is forgetting to create + make a SQL LOGON. You should try and create that logon first, and then when you restore the database, then it will restore the USER logon that you no doubt were using. If you get stuck, you find you often can't delete the "user" logon in that table. So what I often do is re-name it! Then just create a new "user" in security for that database, and then go back to the SQL logon, and use mapping to attached the SQL logon to the SQL "user" in that table. Other then this "often" hiccup, you should be fine.

Go to the top of the page
post Jan 31 2020, 09:09 AM

Posts: 598
Joined: 2-September 03
From: Galway, Ireland

Hi Guys,

Thanks again for extra feedback.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st February 2020 - 11:27 AM