October 4, 2009 at 4:29 pm
Hello all, this is my first post and I am at the VERY bottom on the SQL Server learning curve. My long term aim is to migrate one of my clients MS Access systems to a SQL Server 2008 backend. The resaon being that performance is starting to be an issue. There are about a dozen concurrent users, and the backend tables are about 60MB in size.
OK, so I built a test server (W2K3) and installed SQL Server 2008. BTW, the server is just a Dual Core PC with 4GB RAM, should be OK for testing. I used SSMA to migrate my backend into SS08. On the server, I created a file DSN, copied my Access front end to the server (I installed Access 2002 on the server). When I linked the tables I noticed thay all had dbo in front eg. dbo.tblClients. I had to rename all 50 + tables to remove the dbo but it seemed to work OK. I ran some reports and forms and all looked good.
Here's the problem. On my client PC, I created a file DSN (NOTE: I had to log on to the client PC as Domain administrator) to get the DSN to work. ANyway, when I run even a basic select query, performance is abysmal. Like 10 mins to return any data. Clearly something is wrong but I have no idea how to proceed from here. Any advice would be muchly appreciated.:w00t:
October 4, 2009 at 7:39 pm
The reason all the tables had dbo was that was the table owner.
I'm guessing that the reason you are getting terrible performance is that the access engine is doing all the query work, all of your queries are being done in access and it probably doesn't know about any indexes in SQL.
You probably want to see about moving your queries to SQL stored procedures or queries that are actually executed in SQL and not access..
CEWII
October 4, 2009 at 7:54 pm
Elliot, I'm guessing by your reply that I am wasting my time if I hope to use my Access Front End (which has hundreds of queries, reports & forms) and connect to a SQl backend.
From the research I have done, I thought that SQL did all the processing and returned the results to the calling program (Access). I would like to know if I CAN use my Front End without toooo much alteration.
October 4, 2009 at 9:41 pm
SQL Does the work it is sent to do, and the work it is sent to do is controlled by Access. But if your front-end is querying linked tables then SQL is doing almost no work, Access is doing most of it. I know what you are trying to do can work but I don't think you are going to have much success the way I think you are doing it.
To see exactly what Access is sending SQL you might setup a trace using profile and watch for SQL Statement End events, this should give you the query that Access sent to the SQL Server.
CEWII
October 5, 2009 at 7:07 am
Access only queries move all the data for the tables they're referencing locally to Access and then apply filters & joins to the data. That's why the performance is horrific. The two choices I see most people make (assuming they don't toss Access completely) is to either, copy all the data down to Access first, then run the Access queries, or rewrite the Access queries to make them pass-through TSQL queries. The pass-through queries will do their work on the SQL Server instance you're connected to and then return the results to Access. Assuming well written queries, it should be scads faster than what you're currently seeing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 6, 2009 at 6:55 am
Make sure that your DSN uses the Microsoft SQL Server 2008 Native Client for the data provider, and that it is installed on both the server & the client PC.
October 6, 2009 at 8:45 am
Have you looked into converting your Access front end to a Access .adp project? I have converted all of our company Access databases over to Access projects withe SQL backends. It takes some tome to convert the code, reports and queries over to SQL, but then you have a true client server application.
October 7, 2009 at 6:32 am
I've had similar issues (slowness in Access-SQL Server) that were simply cured by changing the SQL Server name to a fully qualified name (in a domain). You also want to check that the connection to the SQL Server is fast - run ping tests of the server ip and the server name and compare to other servers.....sometimes name resolution issues cause problems...
October 7, 2009 at 7:52 am
I tend to agree with the connectivity portion of this while I disagree with the name resolution part.
Once a connection is open the name is never re-resolved for that connection. Also your local DNS caches the address once it is found. If you have resolution issues it will definitely slow the startup but should more-or-less resolve itself. Worst case is you hardcode a resolution into the HOSTS file so it never has to truly resolve it anywhere else. But don't do something like that without trying other things first.
CEWII
October 7, 2009 at 8:54 am
If you have a DNS issue most of the time it will result in a timeout so you won't be able to connect at all. However you can avoid that altogether by using the ip address of the server, instead of the server name, in the connection string.
Make sure the correct data provider is being used - at least one version of SSMA created the links using "SQL Server" instead of "SQL Native Client". We saw a 10:1 improvement when we found & fixed that. The native client needs to be installed on every PC that connects to the server.
Finally, using a file DSN might not be the best choice, esp if you suspect the network is contributing to the slowness. You can link the Access tables without a DSN.
October 7, 2009 at 3:40 pm
Thank you all for the comments. I now have several things to try at least. The first of which is the Native client DSN. I tried to create a file DSN on a client PC with Windows XP (in the domain), but in the list of drivers for the data source there is no Native client only SQL Server. According to Microsoft you get the Native client driver when you install Sql server 2008. Please tell me I dont have to install SS2008 on the client PC's? If there is a better way to get this driver to the client Pc's, cheers
October 7, 2009 at 4:29 pm
You don't need SS2008 but you do need to install the native client on each PC. If you Google search for "Microsoft SQL Server 2008 Feature Pack, August 2008" the URL will be something like this:
scroll down about 2/3 of the page, the file you want is sqlncli.msi and there are different versions for 32 or 64 bit PC's.
October 7, 2009 at 5:17 pm
Thanks William, I found the native client driver and installed it on the client pc. I tried some basic queries and although performance had improved a little, still nowhere good enough for a production app. William, you mentioned in a earlier post about SSMA perhaps creating connection with Sql Server driver. What do I look for on the server side to check this out. When I open Sql server management studio, I see a reference to Sql Server 10.0.1600 at the top of the tree in the left hand pane. Any advie would be appreciated.
On a slightly different topic, my connections on the client PC side only work when I log on to the PC as domain administrator. When I log on as a "normal" user the connections fail. I have put the Normal user into as many groups on User manager as I can see but still it wont connect. I will need to resolve this moving forward. Cant have the users looging as domain admin.!
October 7, 2009 at 7:36 pm
UtterSQLNewb (10/7/2009)
Thanks William, I found the native client driver and installed it on the client pc. I tried some basic queries and although performance had improved a little, still nowhere good enough for a production app. William, you mentioned in a earlier post about SSMA perhaps creating connection with Sql Server driver. What do I look for on the server side to check this out. When I open Sql server management studio, I see a reference to Sql Server 10.0.1600 at the top of the tree in the left hand pane. Any advie would be appreciated.
For this you need to look at the connection string. In the Access app, start a new query, don't add any tables, switch to SQL view and run this query:
select name, connect from msysobjects where connect is not null
that will give you a list of all the linked tables with the connection strings. Look for the part that says "DRIVER=" if it shows "SQL Native Client" or similar then you're OK. If it says "SQL SERVER" then you would need to change the DSN to use the new driver, then delete the linked tables & create new links. (Linked Table Manager doesn't usually work when the driver is changed.)
October 7, 2009 at 7:59 pm
UtterSQLNewb (10/7/2009)
On a slightly different topic, my connections on the client PC side only work when I log on to the PC as domain administrator. When I log on as a "normal" user the connections fail. I have put the Normal user into as many groups on User manager as I can see but still it wont connect. I will need to resolve this moving forward. Cant have the users looging as domain admin.!
My SQL security know-how is rather shallow, but here goes. . .
When you look at the connection strings (above) if it says "Trusted_Connection=Yes" then you are using Windows authentication, also known as Integrated Security. That means that any user who wishes to connect must either have permission to access the SQL Server, or must belong to a group or role that has permission. Those permissions are managed within SSMS, not from the Windows server. The only exception as you already found out is that the Windows admins group automatically has access to the server. If you have a Windows group created for the users, you can give that group permission.
We have been talking about connecting to the server. You also need to grant permission to the users for SELECT INSERT UPDATE and/or DELETE for each table. This is in contrast to Access, where everybody has permission for everything. HTH
HTH
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply