Any Ideas - ON MIGRATION TO SQL SERVER AND A POSSIBLE .NET FRONT END

[PraizesLiberato]

Hi Developers out there,

We have 4 access databases stored in our shared folder developed by yours truly.

As our table and query structure are quite complex for some of the Databases, also the front end being access itself makes the same very slow.

Just recently we had a discussion regarding the systems we were using and I had suggested we should migrate it to SQL Server and develop a frontend.

Another Idea on the table is: The system could work faster by using MS Access on each individual PC as a frontend and the backend being SQL Server(IS THIS FASTER - As compared to the shared drive).

What would be your thoughts (SINCE SOME OF YOU ALL KNOW A LOT MORE THABN I DO)?

 

[VilaRestal]

Any step will make it faster but some more than others and at considerable cost:

Each user having their own front end will be faster and more stable and is quick and easy.

Converting the back end to SQL server will make things faster (mainly for tables with lots of records or any situation with lots of users - situations where the Jet engine struggles) but will be quite a lot of work (depending on how many tables and relationships there are). It would also open up a whole world of new possibilities (e.g. stored procedures, table-valued user-defined functions, triggers, etc.) but would come with some conversion head-aches.

Converting the front end to VB.NET would be very time-consuming and wouldn't give much performance gains except in certain situations where displaying tables with many tens of thousands of records or more are concerned. It would give other gains though in terms of power and control. But those are always at the cost of speed of development. If Access is good enough in terms of look and feel, stability and security then I'd suggest you stick with it as a front end. Speed is not really a reason to convert from Access to VB.NET except in extreme circumstances.

 

[PraizesLiberato]

Any step will make it faster but some more than others and at considerable cost:

Each user having their own front end will be faster and more stable and is quick and easy.

Converting the back end to SQL server will make things faster (but mainly for tables with lots of records or any situation with lots of users) but will be quite a lot of work (depending on how many tables and relationships there are). It would also open up a whole world of new possibilities (e.g. stored procedures, table-valued user-defined functions, triggers, etc.) but would come with some conversion head-aches.

Converting the front end to VB.NET would be very time-consuming and wouldn't give much performance gains except in certain situations where displaying tables with many tens of thousands of records or more are concerned. It would give other gains though in terms of power and control. But those are always at the cost of speed of development. If Access is good enough in terms of look and feel, stability and security then I'd suggest you stick with it as a front end. Speed is not really a reason to convert from Access to VB.NET except in extreme circumstances.

Wow. Thats a gr8 answer. Truth be told i am a bit relieved to know that i can use ms access as a front end and hence improve the functionality in terms of both the front end and back end being much faster(Than the current setup).

An more pros and cons and ideas of any of the possible stumbling blocks i may encounter would be appreciated.

Praise

 

[PraizesLiberato]

I have very little background in SQL Server but im sure IT here could help us in getting the connection between MS Access and SQL Server. I am also willing to learn the differences and do the conversion of all the systems myself. But we need to be convincing in making our proposal.

Could use some advice to be well prepared on my part as the person incharge. Thanks

Praise John
(MS Access/Asp.net/vb.net/HTML Developer/Designer)

 

[Pat Hartman]

Simply converting the BE from Jet/ACE to SQL server is likely to make the application slower rather than faster if the forms are bound to tables or to queries without selection criteria.

You get no benefit from the BE switch if you don't take advantage of SQL Server's ability to limit the data being sent over the LAN. Jet/ACE is a file manager rather than a database manager and runs locally rather than on the server. It must drag large blocks of data to the local PC to run queries. With SQL server, Access sends the query to the server for processing and the server sends back only the data requested. Binding forms to tables or unqualified queries defeats this completely. Access opens the form as soon as a few rows have come down from the server and then just sits there like a vampire sucking data down until the entire table has been brought to the PC. This is what makes IT departments hate Access. But, you are in total control over this and you can easily make the application data-efficient.

Access is pretty smart and although it has long conversations with the server, it does attempt to send every query to the server for processing. There is no need in the vast majority of cases to create pass-through queries or stored procedures. You do have to be aware of how the interaction with the server works and make sure you don't create queries that can't be passed-through. Jet/ACE convert your Access SQL to T-SQL (in the case of SQL server) and that means that you have to be careful if you use VBA or UDF functions because they can't be passed to the server for processing. If the functions are in the select clause, there is no problem. Jet/ACE apply the function locally after the data is returned from the server. However, in other clauses, Jet/ACE will request entire tables from the server and run the whole query locally.

 

[VilaRestal]

I would take it one step at a time:

Give users their own front end. (Perhaps using the Access Development tools to package that into an msi and using Group Policies to distribute it if you've got lots of workstations.) And see what improvements that makes. There should be slight improvements in terms of opening the database, loading forms, code execution, and other front end things. Opening big tables won't be improved.

Migrating to SQL isn't too difficult but it will take some learning to get used to and there's a few steps. I wouldn't underestimate that part. Do it in development, get the table migration process perfected and saved (to be run at a later date) and get the front end working with this test version. Don't rush it in other words.

Backing it up is also something that needs to be considered. For an Access back end it's easy - just copy the file. For SQL it's easy to do it manually (the BACKUP DATABASE command) but to automate it takes paid for specialist software (Seagate Backup Exec for example).

If I were you I wouldn't consider converting to VB.NET until long after you've done all that and you're sure you need the features it would give and it would be worth the cost in terms of development time.

 

[VilaRestal]

Simply converting the BE from Jet/ACE to SQL server is likely to make the application slower rather than faster if the forms are bound to tables or to queries without selection criteria.

You get no benefit from the BE switch if you don't take advantage of SQL Server's ability to limit the data being sent over the LAN. Jet/ACE is a file manager rather than a database manager and runs locally rather than on the server. It must drag large blocks of data to the local PC to run queries. With SQL server, Access sends the query to the server for processing and the server sends back only the data requested. Binding forms to tables or unqualified queries defeats this completely. Access opens the form as soon as a few rows have come down from the server and then just sits there like a vampire sucking data down until the entire table has been brought to the PC. This is what makes IT departments hate Access. But, you are in total control over this and you can easily make the application data-efficient.

Access is pretty smart and although it has long conversations with the server, it does attempt to send every query to the server for processing. There is no need in the vast majority of cases to create pass-through queries or stored procedures. You do have to be aware of how the interaction with the server works and make sure you don't create queries that can't be passed-through. Jet/ACE convert your Access SQL to T-SQL (in the case of SQL server) and that means that you have to be careful if you use VBA or UDF functions because they can't be passed to the server for processing. If the functions are in the select clause, there is no problem. Jet/ACE apply the function locally after the data is returned from the server. However, in other clauses, Jet/ACE will request entire tables from the server and run the whole query locally.

That's not altogether true of there are a lot users. The improved efficiency of SQL server over Jet generally outweighs the problems you mention in my experience except in massive tables.

And it's more than outweighed in situations where there are a lot of users - something Jet doesn't do well.

But yes, migrating to SQL includes changing to using server-side queries. That's a given IMO, an essential part of the process and not difficult.

 

[Lightwave]

I've just done a bit of this..

Several quick things that you will almost definitely come across.
Getting the raw information in was a bit tricky especially the memo fields / text. I had tried using excel as an intermediary but that didn't do well. I also tried bulk insert in T SQL and I didn't have the permissions for that.

What I found worked well was to create an access front end that was linked to both the SQL Server Target Table and the Access Backend source table and then write a quick append query that took the source and put it in the SQL Target. This was completely effortless however you have to do two things to the SQL Target table. Everything else I tried had big problems with information drop out.

Remove the primary key indicator within the table definition or your target SQL table
Remove the identity feature within the table definition of your target SQL table

SQL Server (2005) has primary key feature but managed completely differently from access.
SQL Server (2005) also has something very similar to autonumber feauture but it is a property of the field and is called Identity (set to YES with increment of 1)

In actual fact the I found the hardest thing about SQL Server was the environment setup.
You want to make sure you have SQL Server Management Studio Express which is a completely different install from SQL Server but hopefully your IT folk will be able to assist in the evironment set up. SSMS will probably need to be installed on your local machine.

Definitely worth doing as the idea of networking a database to theoretically hundreds is super cool.

 

[spikepl]

A word of caution: any project takes longer! Weigh the envisaged benefits, vs. the known and envisaged costs and the "unenvisaged" risks. When a bunch of geeks get together, anything is technically possible. Business-wise not necessarily sensible. So six month later you have all the bells and whistles, but your children have been sold on the market in Marrakesh, just to pay the bills.

1. Do nothing
2. Do something
3. Go the whole hog

Each option has benefits and drawbacks, and a price - think carefully about each.

 

[Simon_MT]

This question I would ask why are there performance issues. To users this is often more important than the development environment.

Simon

 

[Galaxiom]

This question I would ask why are there performance issues.

Indeed. Access per se isn't usually what makes it slow but the way queries are constructed.

SQL Server can be faster but it can also be slower still if the same issues making it slow in Access are carried over to the server based connection.

Learning about how to make Access fast is probably a better initial step and many of the same strategies (though not all) apply to the upscale.

 

[PraizesLiberato]

With SQL server, Access sends the query to the server for processing and the server sends back only the data requested. Binding forms to tables or unqualified queries defeats this completely. Access opens the form as soon as a few rows have come down from the server and then just sits there like a vampire sucking data down until the entire table has been brought to the PC. This is what makes IT departments hate Access. But, you are in total control over this and you can easily make the application data-efficient.

Hi Pat,

I only will be querying required data (Max 100(Prob not) Rows; Min 3) at a time. Would it be ok for Data being bound to a form using a custom query (Generated by the system based on the users clicking on the options/filters/customer in the form). I mean, would the form take time to run through the queried records (Record 1,2,3), after the whole query of 100 records is retrived or would the performance and any filters be quite easy as it would work on a stand alone. How long could such a query possibly take?

 

[PraizesLiberato]

I would take it one step at a time:
Give users their own front end. (Perhaps using the Access Development tools to package that into an msi and using Group Policies to distribute it if you've got lots of workstations.) And see what improvements that makes. There should be slight improvements in terms of opening the database, loading forms, code execution, and other front end things. ---===Opening big tables won't be improved.===---

Hi Vila,

That Could be improved with better queries right?

Don't rush it in other words.

Thanks Vila.

 

[PraizesLiberato]

Indeed. Access per se isn't usually what makes it slow but the way queries are constructed.

SQL Server can be faster but it can also be slower still if the same issues making it slow in Access are carried over to the server based connection.

Learning about how to make Access fast is probably a better initial step and many of the same strategies (though not all) apply to the upscale.

Hi GalaxiomAtHome,

That is probably the approach i may be taking. I have improved our MS Access systems as much as i can. but i still think i could do better. Do You have any good links/Book Names for which i could ref to for more on this subject.

 

[PraizesLiberato]

I've just done a bit of this..

Several quick things that you will almost definitely come across.
Getting the raw information in was a bit tricky especially the memo fields / text. I had tried using excel as an intermediary but that didn't do well. I also tried bulk insert in T SQL and I didn't have the permissions for that.

What I found worked well was to create an access front end that was linked to both the SQL Server Target Table and the Access Backend source table and then write a quick append query that took the source and put it in the SQL Target. This was completely effortless however you have to do two things to the SQL Target table. Everything else I tried had big problems with information drop out.

Remove the primary key indicator within the table definition or your target SQL table
Remove the identity feature within the table definition of your target SQL table

SQL Server (2005) has primary key feature but managed completely differently from access.
SQL Server (2005) also has something very similar to autonumber feauture but it is a property of the field and is called Identity (set to YES with increment of 1)

In actual fact the I found the hardest thing about SQL Server was the environment setup.
You want to make sure you have SQL Server Management Studio Express which is a completely different install from SQL Server but hopefully your IT folk will be able to assist in the evironment set up. SSMS will probably need to be installed on your local machine.

Definitely worth doing as the idea of networking a database to theoretically hundreds is super cool.

LightWave,

Man that adds an ample amount of knowledge on the matter from what you call 'done a bit'.

Thanks for the Part on the Auto number. Sounds like i have a lot to learn on the new features of SQL Server.

Definitely worth doing as the idea of networking a database to theoretically hundreds is super cool.

Yes its is practically as well.

 

[VilaRestal]

That Could be improved with better queries right?

Yes, always.

My point was that giving each user their own front end doesn't help the back end (doesn't speed up queries). What it does do is speed up loading form and report designs and loading code and improves stability.

I agree with everything Lightwave said except:

"Remove the primary key indicator within the table definition or your target SQL table."
I'm not sure you need to and if you do do that, put it back after! Don't have tables without primary keys. That will cause problems.

And another big change you'll notice are date handling. SQL Server stores dates as 'yyyy-mm-dd hh:mm:ss.000' format. That's very different to Access' single precision number of days since 1900.

A lot of query functions will need changing too. IIf changed to the more powerful Case for example.

As I said, it isn't a trivial thing to do but not in the same league as converting Access to VB.NET. So, as Lightwave says, it is well worth doing if you have the time.

However, I reiterate: Backups need thinking about. SQL Server changes what can be done and backups are not straightforward. Backups should be the top priority process in any system. However, that is one potential advantage SQL server has - the possibility of backing up the database while it's being used.

 

[Pat Hartman]

I use the upsizing wizard built into Access. That way I know that everything I have defined will be copied. It copies the data and maintains the autonumbers if you have data to port also. Modifying objects to move them is an accident waiting to happen.

 

[PraizesLiberato]

Hi,

I am thinking of using views for many of my existing queries in MS Access. and then specifically querying those views from the forms. I read up to day that views are stored queries. If that true there has definitely been an improvement in the speed of accessing data from the front end(as compared to the Shared Drives Retrieval of Data)

How is that for an Idea?

 

[PraizesLiberato]

Yes, always.

My point was that giving each user their own front end doesn't help the back end (doesn't speed up queries). What it does do is speed up loading form and report designs and loading code and improves stability.

I agree with everything Lightwave said except:

"Remove the primary key indicator within the table definition or your target SQL table."
I'm not sure you need to and if you do do that, put it back after! Don't have tables without primary keys. That will cause problems.

And another big change you'll notice are date handling. SQL Server stores dates as 'yyyy-mm-dd hh:mm:ss.000' format. That's very different to Access' single precision number of days since 1900.

A lot of query functions will need changing too. IIf changed to the more powerful Case for example.

As I said, it isn't a trivial thing to do but not in the same league as converting Access to VB.NET. So, as Lightwave says, it is well worth doing if you have the time.

However, I reiterate: Backups need thinking about. SQL Server changes what can be done and backups are not straightforward. Backups should be the top priority process in any system. However, that is one potential advantage SQL server has - the possibility of backing up the database while it's being used.

Hi Vila,
could you tell me why the queries wouldn't be any faster as compared to "Accessing the queries stored on a Shared Drive is an MS ACCESS FILE". Since this would be a dedicated access to the server and from my guess this would be better than a shared drive being used for so many other users for different reasons.:banghead:

 

[Galaxiom]

A view is kind of a stored query. However they won't make a big difference if you query them instead of the tables. They are more often used to limit the data provided to certain users or groups. They don't accept parameters so are of limit use.

If you want maximum performance the Stored Procedure is the way to go. These are queries or series of queries and can accept parameters. They can manage very complex actions by creating temporary tables if necessary. Their use is a whole subject in itself.

The first time they run they create and store the execution plan of the queries and this gives a performace gain over sending a query either as a command or via a query in Access.

However with few users isn't as big a gain as one might imagine because a query coming from Access is assessed by the the JET/ACE database engine on the client computer and if possible it is sent to the SQL Server for processing.

There is of course the penalty of that processing and the construction of the query plan but this in not a long time in terms of an individual user experience. Where it really gains is with many users. The server doen't endure the extra load and that makes a big difference when it is under pressure.

I got very good performance opening ADO recordsets on ADO connections against Stored Procedures.