SQL Backend warranted?

[skeeter23]

I am currently developing a system that will eventually handle large portions of job control, tracking, and billing data. This system may be accessed simultaneously by 25 or more users at any given time.

So far the interface, subsequent code, and structure are built on a split access frontend/backend that has always been planned for backend SQL migration.

The latest word from the server DBA's now is almost a complete red light on letting me have proper rights to create the backend. Having to rely on them to make changes and updates for me is entirely unacceptable due to thier "normal" response times and service levels.

Do I fight this fight or simply continue to develop as is on an access backend doing as much as I can to be ready for a move to SQL if/when I can?

My biggest concerns are data integrity, availability, and speed.

 

[HiTechCoach]

Do you have any experience with MS SQL Server development?

Have you ever migrated a split Access app to an MS SQL Server back end?

I find that if you will even want to use an SQL server as the back end then it is best to start SQL back end. This will allow you design your application to take full advantage of stored procedure, T-SQL , triggers, etc. that the MS SQL server offers that is just not possible with JET/ACE (Access database).

 

[skeeter23]

No I have not migrated a backend before. I was forced by management to begin development before I could prove my case to them that I felt we needed a SQL server for this.

Now the server has finally been approved and built (well virtually configured anyway) but I still dont have create priveledges and may not get them anytime soon. Meanwhile we are already past the targeted implementation date. So all I have been able to do is attempt to design as much as I can for a switchover if/when the time comes. I fear that wont be until after we have a crash or lose data.

So I'm still just left wondering if the fight is worth persuing right now or not.

 

[HiTechCoach]

Um ... I an not going to address the "management" issues. I will stick to the Access stuff

No I have not migrated a back end before.

If you have never migrated a back end to SQL server or used an SQL Server back end then how are you testing to be sure you are designing for the switch over?

Just switching your back end to SQL server does not guarantee any performance increase. I often hear from people who see little performace gains. Some even are a lot slower. How is this possible? The most common issue I find is poor design of the front end. In general requesting more data than needed.

This system may be accessed simultaneously by 25 or more users at any given time.

How many users will be need read only access?



If this were my project and I had never use SQL server as a back end, here is what I would do:

1) Split the database if not already done. I will not work on any database that is not split. I start every database split.
My thought on the subject: Splitting your Access database into application and data


2) Install SQL Server Express someplace where you can have control. I would use the same version that you will possible get in the future. You will also want to install the Management tools.

3) use SSMA (Click Here) to migrate your back end to SQL Server

4) Now you can design/develop using both an Access back end and a SQL Server back end. You can relink between back ends to testing.

This will also help you learn about ODBC and how to manage an SQL database. You can't start done this learning curve soon enough.

So all I have been able to do is attempt to design as much as I can for a switchover if/when the time comes.

If you will do the above then you will be able to design as much as you can for a switchover if/when that time comes.

PS: Even if you never make the switchover, you will still learn a lot of valuable skills withing with the SQL Server as a back end.

 

[dportas]

Now the server has finally been approved and built (well virtually configured anyway) but I still dont have create priveledges and may not get them anytime soon. Meanwhile we are already past the targeted implementation date. So all I have been able to do is attempt to design as much as I can for a switchover if/when the time comes. I fear that wont be until after we have a crash or lose data.

Why don't you develop the SQL Server database on your desktop instead of waiting for the server? I recommend you port early rather than wait. You cannot and should not assume that your database design for Jet/Ace is equally suitable for SQL Server or that it will work the same when you port it. Really there's no sense in developing in Jet/Ace if you intend to implement in SQL Server. If nothing else, you'll have a much easier time if you take full advantage of the SQL Server development and management tools.

 

[HiTechCoach]

Why don't you develop the SQL Server database on your desktop instead of waiting for the server? I recommend you port early rather than wait. You cannot and should not assume that your database design for Jet/Ace is equally suitable for SQL Server or that it will work the same when you port it. Really there's no sense in developing in Jet/Ace if you intend to implement in SQL Server. If nothing else, you'll have a much easier time if you take full advantage of the SQL Server development and management tools.

I totally agree.

 

[skeeter23]

Not sure why I never responded back to this but thank you all for the help.

In the end the system ended up being split access only. I am now working on v2.0 which IS being built ground up on SQL