Converting to SQL Server

[Groundrush]

Hi,

Our lovely IT dept without warning as taken it upon them selves to convert the current access database that I manage to SQL server and seeing as I have no clue about SQL server I need some guidance through this process & hope some of you will be happy enough to answer some of my questions.


The database in question is currently in Access 2003 & it was planned to convert to Access 2010
It has a front end on each users PC linked to all the tables in the back end which is on a local server (Not miles away on another server WAN)
I don’t normally have more than 10 of the 25 user logged on the same time
They plan on removing my local server then housing the new SQL converted one on a server in another part of the city


1. Will the database and all its queries, reports, modules, forms etc have to be redesigned or is it simply the case of just running the SQL Server upsizing wizard?

2. My IT dept is telling me that I will not be able to make any changes to my database once converted and taken off my hands, is this correct?

3. Will it be slower to operate compared to it being on the LAN?






I am hoping to still continue managing the front end as often as required allowing any operational changes to be made without any disruption to our users.
This by the way has been one of the major failings of our corporate system that is years behind. The brilliant thing about Access is that you can make inexpensive changes as & when required.

Any tips and tricks will be greatly appreciated.....

 

[SQL_Hell]

1. You will certainly have to make some changes. I can't tell you what though because I have never seen your database.

2. Really? that's their decision. It does sound a bit crippling though, I would bombard them with data change requests and then complain if they are not done quick enough...they'll soon change their minds.

3. Difficult to say, depends how fast your connection is

 

[kevlray]

3. Another thing, I suspect that indexes may need to be set up on the tables on the SQL Server.

 

[SQL_Hell]

Agreed Kevlray, this is an area often overlooked by people beginning SQL Server, and it really is one of the biggest areas for performance improvement.

I added a covering index to a SQL server table with 250 million rows in it last night. It turned a 3 minute query into a query that executes in 0.5 seconds!

 

[Rx_]

Sorry to hear that Russian IT invaded your homeland DB.
I think it is time to have a discussion with a business manager about lost productivity and lost opportunity.
They need to respond as to how they converted it and provide the error files.
Microsoft SQL Server Migration Assistant for Access is probably the best tool to use. It provides an analysis of what converted, index, and other pass/failures.

SQL Hell is right that if SQL is migrated properly, it can be a good thing.
Being the IT group did this with out a written plan or providing migration summary, you can't be sure your data was actually migrated.

There are different ways to connect with linked tables.
For the number of users, a script with a connection string would be your best option.
This forum has some good examples if you search.
My personal preference is to download the free SQL Server Native Client and install it on each client's PC, then write a script to connect it. Others will do the same with the built in Windows ODBC driver.

You have a lot to look into at this point. Good luck.

 

[Groundrush]

Thanks guys, looks like I have a lot to think about.
will come back with more questions

 

[Rx_]

Just wondering how your mission to convert is going? We would like to hear from you.
- Regards

 

[Steve R.]

While I was still working, I up-sized an MS Access database to SQL server. It was a very simple database. The process went smoothly.

1. Will the database and all its queries, reports, modules, forms etc have to be redesigned or is it simply the case of just running the SQL Server upsizing wizard?

In my case the tables were imported directly into SQL Server. I also used this up-sizing opportunity to redesign many aspects of my database, so some "new" forms, reports, and modules were created. Some forms, were left "as is", in that case the only issue was correctly identifying the data source and verifying that the field names were still valid.

2. My IT dept is telling me that I will not be able to make any changes to my database once converted and taken off my hands, is this correct?

Welcome to the gestapo world of the IT department.:banghead:. Yes, I "lost" the ability to work directly with the tables. Fortunately, I had one person from IT assigned to help me. Not much had to be changed, with the tables since the database was already operational from the MS Access perspective and was very simple.
My condolences to the poor person from my old department who gets the honor of working with the IT department to make future modifications to the table structure!

As an editorial note: Make sure that you keep control of the MS Access front-end. The IT department, in my case, did not balk at that. That allows you to retain the ability to modify the program (but not the tables) as you see fit.

3. Will it be slower to operate compared to it being on the LAN?

My experience is that working with SQL Server as a back-end was faster and more reliable.

We used an OBDC connection between MS Access and SQL Server.