Questions about moving to SQL backend

[cricketbird]

Our database (split fe/be, 80 users, Office 365, in use for 6 years, regular small modifications to add features or new reports) has become mission-critical enough that folks are rightly nervous about the backend living "loose" on a shared network drive. I'm being asked to move it to SQL server. I would not be an admin on the SQL server - all requests will have to go through our very slow and bureaucratic IT folks (as in weeks to months to get a request through).

Some questions:
1) To future-proof the data, I was thinking I'd add a bunch of dummy columns to each table as well as a few dummy tables. This way, I don't have to go through IT when I need a new field or table (at least for a while). Is this an okay approach or a terrible idea?

2) We very rarely (but not never) have our network go down. I currently have it set up so that users launch a batch file which copies the front end to their C drive and makes a copy of the data (backend) on their C drive, but DOES NOT USE that data. There's an option within the database itself to switch to the backup data if needed with appropriate warnings that any changes will not be saved (mostly our users need to SEE the data, not interact with it). It's a crutch, but it helps us continue to operate if the system is down. Would we lose this ability to work offline if the network is down? Or would there be a way to create a local copy of the data and switch to it as needed? The data itself is not sensitive and nobody has a problem if there are copies in different places.

3) We have external images for each product located on the network drive and linked to within the database. Would we still be able to link to those if the rest of the data was on SQL server?

4) In general, does moving to a server offer speed gains or declines?

Thank you!

 

[plog]

First and foremost--let it go. This is no longer your database, its been absorbed by the bureaucratic monolith. It will be hard, but when issues arise--and they will--- do not feel responsibility because they took away your power to resolve those issues. I've been there, it will be hard, but you have to accept your new role.

1. Bad idea. Let the new system figure out how to deal with future issues. I've been the guy after the guy who tried to "future proof" a database. It was a huge pain to clean up. Plus, future issues usually aren't fixed by new fields, but new tables. It's more likely not going to save anything, but make a mess.

2. Bad idea. The new system is the new system. Synching data is such a pain. Not to harp on it, but this is no longer your responsibility. Let the new system be the new system.

3. Yes. The file location is the file location. The database isn't really involved in this, the operating system is and as long as the file location still exists the computer will still find it.

4. No speed difference. I mean you were using the network for front end back end before, so you're still travelling over the server for your data. This realy won't have any effect. However, you might want to ask them to do a trial run---set up the back end, populate it with data and let you play with it for a month so that everything will work for actual usuers when you guys decide to go live with it.

Again, emotionally let it go. This is no longer your database.

 

[tvanstiphout]

> very slow and bureaucratic IT folks
That is a common attitude, but not a productive one.
If changes are requested, and they are high priority to the organization, the hierarchy will tell IT what their priorities are. It is your responsibility to know what the process is for submitting change requests to IT, and what possible escalation steps you can take (if any) when expectations are not being met. The rest is not your business.

For development, you may want to install the free Developer edition of SQL Server on your machine, so you can test any change scripts you submit to IT. You're looking good if those scripts work the first time.
A bit more fancy would be to use Visual Studio's "SQL Server Database Project" which can assist with making scripts.

 

[GPGeorge]

Our database (split fe/be, 80 users, Office 365, in use for 6 years, regular small modifications to add features or new reports) has become mission-critical enough that folks are rightly nervous about the backend living "loose" on a shared network drive. I'm being asked to move it to SQL server. I would not be an admin on the SQL server - all requests will have to go through our very slow and bureaucratic IT folks (as in weeks to months to get a request through).

Some questions:
1) To future-proof the data, I was thinking I'd add a bunch of dummy columns to each table as well as a few dummy tables. This way, I don't have to go through IT when I need a new field or table (at least for a while). Is this an okay approach or a terrible idea?

2) We very rarely (but not never) have our network go down. I currently have it set up so that users launch a batch file which copies the front end to their C drive and makes a copy of the data (backend) on their C drive, but DOES NOT USE that data. There's an option within the database itself to switch to the backup data if needed with appropriate warnings that any changes will not be saved (mostly our users need to SEE the data, not interact with it). It's a crutch, but it helps us continue to operate if the system is down. Would we lose this ability to work offline if the network is down? Or would there be a way to create a local copy of the data and switch to it as needed? The data itself is not sensitive and nobody has a problem if there are copies in different places.

3) We have external images for each product located on the network drive and linked to within the database. Would we still be able to link to those if the rest of the data was on SQL server?

4) In general, does moving to a server offer speed gains or declines?

Thank you!

When your successful project graduates to a SQL Server back-end, take a few moments and celebrate that success! You've earned the respect of your organization. They consider the application an asset and plan to continue using it.

When the DBA is assigned to manage the database on SQL Server, introduce yourself and invite that DBA to coffee (or an adult beverage is that's appropriate) and explain how much you are counting on their expertise to grow and enhance what you started and ask that DBA for tips on making their job easier. Follow through on that offer whenever possible.

You want to be part of a larger team, not the unhappy person who's baby grew up and left home.

 

[cricketbird]

First and foremost--let it go. This is no longer your database, its been absorbed by the bureaucratic monolith. It will be hard, but when issues arise--and they will--- do not feel responsibility because they took away your power to resolve those issues. I've been there, it will be hard, but you have to accept your new role.

1. Bad idea. Let the new system figure out how to deal with future issues. I've been the guy after the guy who tried to "future proof" a database. It was a huge pain to clean up. Plus, future issues usually aren't fixed by new fields, but new tables. It's more likely not going to save anything, but make a mess.

2. Bad idea. The new system is the new system. Synching data is such a pain. Not to harp on it, but this is no longer your responsibility. Let the new system be the new system.

3. Yes. The file location is the file location. The database isn't really involved in this, the operating system is and as long as the file location still exists the computer will still find it.

4. No speed difference. I mean you were using the network for front end back end before, so you're still travelling over the server for your data. This realy won't have any effect. However, you might want to ask them to do a trial run---set up the back end, populate it with data and let you play with it for a month so that everything will work for actual usuers when you guys decide to go live with it.

Again, emotionally let it go. This is no longer your database.

Thank you for seeing through my questions to the real issue - it is darn hard to let a beloved project go off into the wild without me. Thank you for your advice and feedback.

 

[cricketbird]

When your successful project graduates to a SQL Server back-end, take a few moments and celebrate that success! You've earned the respect of your organization. They consider the application an asset and plan to continue using it.

When the DBA is assigned to manage the database on SQL Server, introduce yourself and invite that DBA to coffee (or an adult beverage is that's appropriate) and explain how much you are counting on their expertise to grow and enhance what you started and ask that DBA for tips on making their job easier. Follow through on that offer whenever possible.

You want to be part of a larger team, not the unhappy person who's baby grew up and left home.

Thank you for helping me see this as a success and not a "taking over" and for the positive suggestions. I appreciate it!

 

[cricketbird]

> very slow and bureaucratic IT folks
That is a common attitude, but not a productive one.
If changes are requested, and they are high priority to the organization, the hierarchy will tell IT what their priorities are. It is your responsibility to know what the process is for submitting change requests to IT, and what possible escalation steps you can take (if any) when expectations are not being met. The rest is not your business.

For development, you may want to install the free Developer edition of SQL Server on your machine, so you can test any change scripts you submit to IT. You're looking good if those scripts work the first time.
A bit more fancy would be to use Visual Studio's "SQL Server Database Project" which can assist with making scripts.

Thanks for the helpful advice

 

[Pat Hartman]

4) In general, does moving to a server offer speed gains or declines?

It depends. If all your forms are bound to tables or unfiltered queries, you will almost certainly find the app to be slower with a SQL Server BE than it was with ACE. If that was your design style, you will need to modify the forms so that they are bound to queries with WHERE clauses that filter the records. No user is going to work with 100,000 records. That means, you don't need to bring 100,000 across the wire. Your queries should select the absolute minimum number of rows and columns. We can help with this if you need to make this switch. If your data volume is still low, you may not experience the slowness immediately but with every new record you add to the BE it will get slower until it becomes noticeable.

I agree with the others - congratulations!!! This is a happy day for you.

As the others have suggested, talk to the DBA and see if he will allow you to have access to the test server. This will allow you to do the conversion yourself and clean it up before you have to turn it over to the DBA. Once the app is in production, you will still be adding features but you shouldn't have to be changing the BE on a daily basis. When you do have BE changes, you need to consolidate them and agree with the users on a once or maybe twice a month release schedule. With a release schedule that frequent, most of your changes will be pretty small and the DBA shouldn't have a lot of trouble with them. Your procedure will be making the schema changes yourself in the test region and testing them. Then the DBA will move the changes from test to the Systems test region and your users will test the changes. Once everyone approves, the DBA will move the changes to the production region and make them available to all users. The point is - you can't be asking him to make tiny changes every day. You will need to batch your changes into a schedule you can both agree to and he will be able to support.

If the DBA will not allow you to have a test region to work in and allow you to actually make schema changes in that environment, you have no alternative but to install SQL Server Dev (or Express) on your computer and develop there.

 

[CJ_London]

I would also clarify who will be responsible for the transition and user testing - you or IT?. Don't forget your app will (presumably they are not rewriting that) 'simply' connect to a different back end. If the latter ensure you provide a full specification of performance requirements signed off by everyone concerned (you, IT, management)- this includes the purpose of the app, how the data interacts with other data, users, imports and exports, data security, speed of response, continuity of service, etc. Regardless, having such a document is worthwhile so everyone knows what to expect. Saves a lot of time in the long run.

I developed an app for a large utility company which they subsequently decided to place the BE on sql server. I wrote such a document, they thought they knew better and completely messed it up and it took them nearly 3 years to get it right. For another client (pharmaceuticals) they listened, sought clarification where required and completed the transition in about 6 weeks.

With particular reference to point 4. I had another client who transitioned to sql server, then transitioned back due to the awful performance. This was because sql server prioritised other applications which meant they only got the 'nub end' and IT/management were unwilling to redistribute resources or invest in additional resources - so per para 1 - make sure IT/management sign up to committing to providing the required resources.

When the tables are constructed in sql server, there will be differences, in part IT may have their own rules around naming convention, use of lookup tables etc. If your app already communicates with other apps already in sql server, there are likely to be changes there as well. Not to mention there are some differences in field datatypes (e.g. booleans are 1 and 0, not -1 and 0). Plus sql server queries are generally more flexible than Access and do some things differently - any domain functions and functions like iif, switch and choose used in access will have to be converted for example.

 

[adhoustonj]

There is some good stuff in this thread.

I will say - I'm in this same position. I'm a DBA of 20 of the big babies, and IT wanted them out of the house +2 years ago. How many Access DB's do we have? >1000. How many access db people? 1. In reality many of the >1000 can be replaced with jobs on MRP servers, different middleware servers, etc. Even just stored procedures that run on whatever schedule needed. Most.

My IT doesn't seem to fond on even keeping a front end Access, RunTime only as a last resort mission critical. We are investing in +$2,500 annual subscriptions for each workstation - where many apps have 50-200 annual users. And guess what - these MES no/low code front ends.. Well yeah, there isn't much to say. Most I've seen are built with the intent to utilize 1-2, stretching at 3, up to 5 tables - me being generous.

I just don't see moving away from Access front ends. Part of me wants to keep learning .Net or ASP, something, anything where I know we can continue to support the kids after they have moved out of the house.

But like everyone has said --- This is kind of good news. These are important to the Org enough that they are graduating to SQL Server.
Are they going to be doing all of the front end changes/requests/modifications that users request like new features, enhancements, bugs that pop up?

With the biggest system I am the DBA over - Speed improvements were 11,000% after migration and setting up right on SQL server, which I did with the help of the people here.

 

[Galaxiom]

functions like iif, switch and choose used in access will have to be converted

SQL Server has IIF(). I think it is basically a wrapper for CASE.