July 8, 2015 at 4:57 am
Where I work we have several SQL Server databases. Well over a hundred. Most are SQL Server 2008 R2, but some are still in SQL Server 2005. One of these applications that I'm partially responsible for, using SQL 2005. Before I came on-board, they hired a contractor to write a warehouse application. He only knew MS Access, so he wrote the app using MS Access 2007 against the SQL 2005 database. As I understand it there's several hundred employees using this application potentially at the same time.
Now we've got a very serious situation in which sometimes the Access application hangs in one module, which is related to the inventory out table. The application will error out with a timeout message. When I get into SSMS and check sp_who2 I really don't see any locks there at all, but the application is badly hung. On the worse occasion it was hung for 2 days. My guess is that it's the Access application that somehow is hanging itself, because at least as far as my limited SQL management skills go, it appears as though there's no locks in the database.
But I've been wondering would things improve if we went from SQL 2005 to SQL 2008 R2? My co-worker on this project believes that there are significant differences in data types from SQL 2005 to SQL 2008 R2, so he's reluctant to migrate the database. I have no idea if he's right or not. So I put it to you, are the differences in data types from SQL 2005 to SQL 2008 R2 that big? If so, what are the gotchas that we need to be aware of?
Kindest Regards, Rod Connect with me on LinkedIn.
July 8, 2015 at 7:42 am
the 2008 links to MS' "what's new in 2008" seem to be broken. That said - here's a reasonable inventory of the changes by version:
http://www.c-sharpcorner.com/Blogs/15967/differences-between-sql-server-2005-2008-2008r2-2012.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 8, 2015 at 7:53 am
Rod at work (7/8/2015)
Where I work we have several SQL Server databases. Well over a hundred. Most are SQL Server 2008 R2, but some are still in SQL Server 2005. One of these applications that I'm partially responsible for, using SQL 2005. Before I came on-board, they hired a contractor to write a warehouse application. He only knew MS Access, so he wrote the app using MS Access 2007 against the SQL 2005 database. As I understand it there's several hundred employees using this application potentially at the same time.Now we've got a very serious situation in which sometimes the Access application hangs in one module, which is related to the inventory out table. The application will error out with a timeout message. When I get into SSMS and check sp_who2 I really don't see any locks there at all, but the application is badly hung. On the worse occasion it was hung for 2 days. My guess is that it's the Access application that somehow is hanging itself, because at least as far as my limited SQL management skills go, it appears as though there's no locks in the database.
But I've been wondering would things improve if we went from SQL 2005 to SQL 2008 R2? My co-worker on this project believes that there are significant differences in data types from SQL 2005 to SQL 2008 R2, so he's reluctant to migrate the database. I have no idea if he's right or not. So I put it to you, are the differences in data types from SQL 2005 to SQL 2008 R2 that big? If so, what are the gotchas that we need to be aware of?
Not sure what your coworker is referring to. It seems the biggest bottleneck is "hundred employees using this application potentially at the same time" when the application in question is Access and the data lives in sql. Depending on the connection this can turn set based queries into RBAR. I have seen this happen with an application I worked on around here. We had a query in Access that was set based manipulation of the data. However, when running a trace it was doing it row by agonizing row and it took hours and hours to complete. I moved the exact same sql to a stored proc and the process ran in just a couple seconds.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2015 at 8:14 am
I doubt there's data type changes that would make it hard to move this to SQL2008.
I agree with Sean. I'd focus on getting as much of the logic out of Access and onto SQL Server. I'd then focus on getting Access out of the picture.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 8, 2015 at 8:40 am
Thank you Matt, Sean and Alvin. So it seems like, at least as far as the data types go, there's really nothing to worry about, if we go from SQL 2005 to SQL 2008 R2.
However it also seems to me as though you're recommended that we dump the MS Access frontend ASAP. Because that is the real bottleneck.
Kindest Regards, Rod Connect with me on LinkedIn.
July 8, 2015 at 9:05 am
SQL Server 2008 did add several new data types (see this link) (date, time, datetime2, hierarchyid, geometry, geography) . However, nothing in the existing data types was changed that would prevent an upgrade from 2005 -> 2008. In fact, doing something like this would be counter-productive for Microsoft to do.
You mentioned that
they hired a contractor to write a warehouse application. He only knew MS Access, so he wrote the app using MS Access 2007 against the SQL 2005 database.
You might want to look into using SSAS and a true data warehouse.
Oh, and I agree with Sean and Alvin - get the logic out of Access. Have Access call stored procedures in SQL Server, and ensure that those stored procedures are tuned (and not doing RBAR operations).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 8, 2015 at 11:55 am
WayneS (7/8/2015)
SQL Server 2008 did add several new data types (see this link) (date, time, datetime2, hierarchyid, geometry, geography) . However, nothing in the existing data types was changed that would prevent an upgrade from 2005 -> 2008. In fact, doing something like this would be counter-productive for Microsoft to do.You mentioned that
they hired a contractor to write a warehouse application. He only knew MS Access, so he wrote the app using MS Access 2007 against the SQL 2005 database.
You might want to look into using SSAS and a true data warehouse.
Oh, and I agree with Sean and Alvin - get the logic out of Access. Have Access call stored procedures in SQL Server, and ensure that those stored procedures are tuned (and not doing RBAR operations).
Agreed with everyone above - Access wasn't built for 200+ folks, even if it's for something simple like inventory/warehouse management. Find a way to replace it.
In the short term, you might care to look at giving all users their OWN copy of the access file, assuming ALL data is being stored in SQL Server. Simply doing that will help a LOT with the slowness (the access file tries to manage internal locks and quickly gets overwhelmed with that many concurrent users); the local locks are meaningless if the data is in SQL server.
I've had to play with a similar scenario in the past. This helped get through the transition:
1. provide a COMPILED access project.
2. use the Access "publish and deploy" wizard to create a click-once which pushes the MDE out. The click-once will create a central install point from which each user can seamless "install" it to their machine.
3. make sure the users are firing the LOCAL copy of the MDE.
It won't be as good as a true application but it should cut down on the disaster you're up against right now (giving you some time to get your application rebuilt).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 9, 2015 at 8:29 am
Matt, I definitely like your suggestion of giving the users a compiled version of the .MDB. Right now what some of them do (fortunately not many) is when they're in the Access application if they want to "tweak some data", then they'll bang out of the app and just use Access as a front end to the database. They'll do directly into the tables and change whatever it is they think the data should be.
Kindest Regards, Rod Connect with me on LinkedIn.
July 9, 2015 at 8:44 am
Rod at work (7/9/2015)
Matt, I definitely like your suggestion of giving the users a compiled version of the .MDB. Right now what some of them do (fortunately not many) is when they're in the Access application if they want to "tweak some data", then they'll bang out of the app and just use Access as a front end to the database. They'll do directly into the tables and change whatever it is they think the data should be.
Then they need to change their mindset. This is not an easy step but one that is required if you want to maintain data integrity. Many "power users" think they understand the database but the reality is they don't most of the time. There are often referenced values or other piece of information that need to be updated when changing values. This is why we have UIs, to prevent the users from screwing up the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2015 at 8:29 pm
Sean Lange (7/9/2015)
Rod at work (7/9/2015)
Matt, I definitely like your suggestion of giving the users a compiled version of the .MDB. Right now what some of them do (fortunately not many) is when they're in the Access application if they want to "tweak some data", then they'll bang out of the app and just use Access as a front end to the database. They'll do directly into the tables and change whatever it is they think the data should be.Then they need to change their mindset. This is not an easy step but one that is required if you want to maintain data integrity. Many "power users" think they understand the database but the reality is they don't most of the time. There are often referenced values or other piece of information that need to be updated when changing values. This is why we have UIs, to prevent the users from screwing up the data.
I had this exact problem. Ended up having to play some real magic to hide the un-compiled version AND hide the link tables (there are a few neat tricks there whereby the compiled copy won't show ANY links) and even then I ended up creating a service account I embedded into the connection string for the linked tables and removed all user perms to the SQL side. They finally got the hint at that point.
Like Sean mentioned - it seems harsh, but we had too many issues with data getting monkeyed with and no audit trail. I was dealing with PHI in a healthcare setting at the time, so I really didn't have the luxury of "letting them" play with the data. I wasn't popular for a while, but they got over it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2015 at 4:00 pm
Alvin Ramard (7/8/2015)
I doubt there's data type changes that would make it hard to move this to SQL2008.I agree with Sean. I'd focus on getting as much of the logic out of Access and onto SQL Server. I'd then focus on getting Access out of the picture.
+1 , I've seen this situation in a past company I worked for where Access had the logic at the client instead of calling stored procedures. The amount of data was not big by any standard yet the amount of slowness and hiccups where disproportionate to what I normally see.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply