The law firm I work for is about to go through some major software/hardware
changes that will include several new applications needing to use SQL
server. I definately want to make sure I make the right decision as to what
hardware and licensing choices are right for us (in other words I dont want
to be talked into buying anything we dont need).
The applications that need the use of a SQL server are:
Imaging database (database for scanned legal documents)
Firm database (stores the firms software application data)
Dialer database (works with a predictive dialer)
There are roughly 80 users on the network...with almost all accessing the
Firm database...Imaging only used by a few but contains alot of data...The
dialer would only account for about 20 users but as you can imagine would be
put to constant use.
Would it be logical to put all this load on one server? What solution would
you recommend from a cost perspective? A performance perspective?
Any tips would be greatly appreciated.
DaveHi Dave
As I don't know any of these applications, the best estimates will be those
provided by a vendor or partner. You can always get more than one quote and
award your business to the best tender.
If you are already using these applications then you should be able to get
some idea of sizing for the databases and predict possible growth rates.
You may have to do some load testing as part of the test cycle to make sure
the supplied system meets your capacity specifications.
Putting them on the same server would mean that they would share resources,
it would also mean that it would be very difficult to conclude that any one
application supplier was causing a particular issue. Having separate systems
may mean additional up-front costs for hardware and software licences.
What ever system(s) you opt for, you may want to make sure that you have
options to expand without having to totally change the hardware again.
John
"Dave" wrote:
> The law firm I work for is about to go through some major software/hardware
> changes that will include several new applications needing to use SQL
> server. I definately want to make sure I make the right decision as to what
> hardware and licensing choices are right for us (in other words I dont want
> to be talked into buying anything we dont need).
> The applications that need the use of a SQL server are:
> Imaging database (database for scanned legal documents)
> Firm database (stores the firms software application data)
> Dialer database (works with a predictive dialer)
> There are roughly 80 users on the network...with almost all accessing the
> Firm database...Imaging only used by a few but contains alot of data...The
> dialer would only account for about 20 users but as you can imagine would be
> put to constant use.
> Would it be logical to put all this load on one server? What solution would
> you recommend from a cost perspective? A performance perspective?
> Any tips would be greatly appreciated.
> Dave
>
>|||Dave wrote:
> The law firm I work for is about to go through some major software/hardware
> changes that will include several new applications needing to use SQL
> server. I definately want to make sure I make the right decision as to what
> hardware and licensing choices are right for us (in other words I dont want
> to be talked into buying anything we dont need).
> The applications that need the use of a SQL server are:
> Imaging database (database for scanned legal documents)
> Firm database (stores the firms software application data)
> Dialer database (works with a predictive dialer)
> There are roughly 80 users on the network...with almost all accessing the
> Firm database...Imaging only used by a few but contains alot of data...The
> dialer would only account for about 20 users but as you can imagine would be
> put to constant use.
> Would it be logical to put all this load on one server? What solution would
> you recommend from a cost perspective? A performance perspective?
> Any tips would be greatly appreciated.
> Dave
>
Hi Dave,
Based on your questions, I would suggest you hire a Consultant to plan
things "right from the start". Best of luck to you on this project.
Showing posts with label software. Show all posts
Showing posts with label software. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Help with SQL 6.5
I know this is an old version but the customer cannot upgrade at this time due to a Mac software issue. I am a newbie so forgive me if I ramble. Here is my question.
Windows Server 2003
SQL running version 6.5
Log size =1998 MB
Log Avail=600 MB
No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.
THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.
Event Type: Error
Event Source: MSSQLServer
Event Category: (2)
Event ID: 17060
Date: 8/4/2006
Time: 7:51:51 AM
User: N/A
Computer: AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.
Any ideas? Thanks!!What are the errors that the users are getting?
And the all important question, what changed, and who changed it?|||There is insufficient system memory to run this query..I am not sure what changed...|||Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?|||Ok, there are a few different ways to solve this problem.
The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.
The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.
A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.
There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.
-PatP|||To answer Mcrowley...it happens every cpl days...all are not able to log in...|||Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.
If you can afford the daily reboots, then the simple answer is to just schedule a script to restart (http://www.microsoft.com/technet/scriptcenter/scripts/desktop/state/dmstvb07.mspx) the server.
-PatP|||the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!|||The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.
You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.
-PatP|||Thanks Pat...would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.|||Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.
SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"
-PatP
Windows Server 2003
SQL running version 6.5
Log size =1998 MB
Log Avail=600 MB
No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.
THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.
Event Type: Error
Event Source: MSSQLServer
Event Category: (2)
Event ID: 17060
Date: 8/4/2006
Time: 7:51:51 AM
User: N/A
Computer: AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.
Any ideas? Thanks!!What are the errors that the users are getting?
And the all important question, what changed, and who changed it?|||There is insufficient system memory to run this query..I am not sure what changed...|||Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?|||Ok, there are a few different ways to solve this problem.
The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.
The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.
A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.
There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.
-PatP|||To answer Mcrowley...it happens every cpl days...all are not able to log in...|||Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.
If you can afford the daily reboots, then the simple answer is to just schedule a script to restart (http://www.microsoft.com/technet/scriptcenter/scripts/desktop/state/dmstvb07.mspx) the server.
-PatP|||the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!|||The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.
You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.
-PatP|||Thanks Pat...would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.|||Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.
SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"
-PatP
Subscribe to:
Posts (Atom)