I have an Active/Active/Passive cluster with 64GB RAM on each node running SQL 2000 EE, AWE is enabled as well as the PAE switch, all is dandy with that.
Question:
Should I configure each SQL Instance to have only a max mem usage of 32GB in the event both failover to the same node ? or will the memory allocation be handled without any issue if each node is configured to use 64GB ?
Thanks.
So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -4x PowerEdge 68004x dual core x64 procs in each box32GB ram in each boxWindows Server 2k3 R2 Enterprise x64 SP2SQL 2005 Enterprise x64 SP2Active Active Active Passive cluster (4 boxes, 3 instances)dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instanceNow, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.
View 14 Replies View RelatedOn first Node A: The server has 16 GB of physical RAM.Â
On second Node B: The server has 10 GB of physical RAM.
Now, this being Active Active, Node A can be clustered on failure onto Node B..Now reporting server is configured under these two nodes, with defined MAX and MIN server memory of 12 as MAX and 0 is min IN GB.Now with this setting on SQL whenever the cluster moves, such config make OS goes low on node for 10 GB.I am only left with option of switching this MAX and MIN to a default setting or is there any other alternative such as script which can change this setting accordingly when cluster moves to respective server.
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
I am looking for some recommendations for memory sizing and options for a SQL 2000 Cluster. This is a two node cluster built on Windows 2003 ENT SP1 (x86). Both the nodes have the following hardware:
- 4 x Dual Core AMD Processors
- 16 GB Memory
- EMC Shared Disk
We are running six SQL 2000 instances and don't expect each of these instances to use more than 1.7 GB of memory. All these instances are going to support BizTalk 2004 Databases. I already have /PAE enabled on the nodes. I am looking for the following answers:
- Do I need to enable AWE on all the instances even if the instances ? Currently, we don't have that enabled and we have seen some issues regarding excessive paging even when there is physical memory available. The DBAs think that we don't need to enable AWE. I am bit confused on this one.
- We normally run 3 instances on each node and would like size the cluster in such a way that it can take six instances in case of a node failure
Any input will be highly appreciated.
I have SQL 2005 Enterprise 64bit SP2 on windows 2003 EE 64bit SP2 w/24gig memory. My problem is that it wont use more than 110mb of memory according to task manager processes and performance. I've tried messing with the min and max and rebooting each time I make the change and sill nothing. Currently Ive changed it back to default. The server shows up as 24gig so it is seeing the memory. Any ideas? I thought 64bit was supposed to be better about this kind of thing.
Also, AWE has always been unchecked as this is a 64bit box.
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
Hi all,
We have SQL Server 2005 Entp 32-bit on Windows Server 2003 Entp.Edtion 64-bit. ( Total memory = 16 Gb)
1) So, in this case , do i need to enable AWE Configuration from SQL Server Side ( 32-bit).
And what settings( AWE, /PAE) i should enable on OS (i.e.) on Windows Server 2003 (64-bit) in boot.ini file.
Because right now, i dont see any switches in boot.ini file.
2) And with 16 GB of RAM, what should be the ideal setting for memory ( Min & Max).
Please give some ideas on this.
Thanks.
We have a new set up on VM to run an application running 24*7 (migrated from SQL server 2008R2) with below configs:
1. OS- Windows server 2012 Standard 64 bit hosted on Virtual Machine
2. Memory 16 GB and Cores =4 with 2.4GhZ processor
3. SQL server 2012 SP2 , 64 bit Standard edition.
4. Total size of databases as of now 15 GB with biggest being 5 GB.
How should i go around in setting the MAX and MIN server memory settings. I have this set up for many of SQL 2005 and 2008R2 servers, but for 2012 i heard that things has slightly changed.Â
How should i start analyzing and setting the right value of this MAX and MIN?
Hi,
I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far:
No maximum - one instance used about 12GB of RAM so then the others struggled
Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused.
Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances.
I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB.
How do I go about configuring SQL Server to behave like this? Is it possible?
Thanks.
I have two Windows 2000 servers (Advance Edition) to form a Windows Cluster. I also install MS SQL 2000 Enterprise Edition on the cluster to form a MS SQL cluster. Now, I want to upgrade the hardware and OS (but keep on using SQL 2000), so I install Windows 2003 server Enterprise Edition on two new servers to form a new Windows Cluster. I am planing to install MS SQL 2000 Enterprise Edition on the new cluster, so the old SQL cluster and new SQL cluster are side by side. I would like to know how to setup a new SQL cluster (I know it has problem to rename SQL Cluster name, so how to fix this problem)? And how to transfer everything (such as system databases, users database, sql user account, password and maintenance plan jobs etc) from old SQL cluster to new SQL cluster? And how to switch over from old SQL cluster to new SQL cluster?
Thanks a lot !
I have Two Dell dual Itanium 64 with 16 Gb of Ram each Running a default and a named instance of SQL. The Issue is that I have a bottleneck somewhere and noticed that the memory usage for sqlservr.exe is never over 120000k. I have adjusted till I'm blue in the face to get this memory to expand. My question is does this sound reasonable? Or I should look at other possible bottlenecks?
View 1 Replies View Relatedsql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
We have a win2003 cluster with sql 2000 with 16 gig of memory. Should you leave more memory for the OS versus if this was a stand-alone server
View 3 Replies View RelatedDear all,i am planing to implement a Windows 2003 Cluster with MS SQl 2000Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GBRAM per Node.I have the need for 6 SQL instances and would like to implement aactive-active cluster. What do you think it the best way to configurethe memory for the Servers ?I would like to run 3 instanced per Node but can i allocate 7 GB perNode for SQL (and 1 for the Operating System) ?What is happening then i one server fails ?Should i plan to allocate only 3 GB per Server for SQL to make surethat one server can handle the load for all instances if one serverfails ?Should i use the /AWE switch only in the boot.ini to allow more than 4GB Memory ?or should i use the /3 GB switch as well ?Maybe somebody can give me a hint.Best regards,Walter
View 1 Replies View RelatedWe recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:
32-bit Windows Server 2003 SP1
SQL 2005 SP2, 2-node cluster
16 GB memory on each node
/3GB /PAE switches in the boot.ini file
AWE Enabled, min memory=0, max memory=14000
The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?
I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?
Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.
Chris
I will doing an installation of MS SQL 2000 that will be used by multilanguage web sites.The languages are Japanese, German and of course English.Currently I'm planning to install MS SQL 2000 standard version butafter reading some articles I'm considering the Enterprise version.Can someone shed some light on the advantages of using Enterprise vsStandard strictly from a language support perspective?Also, is there any caveats or other considerations that I should keepin mind to make sure that the developers have all they need to developthese multi language sites?Thanks in advance.
View 1 Replies View RelatedWe are running with a 2 node windows cluster having three SQL instances on it.Â
OS: Windows server 2008R2 SP1
SQL : SQL server 2008R2 (10.50.6529)
Currently both nodes have 256 GB or memory and we are having multiple auto failover for resources. What will be the best practice for OS memory reservation (OS+tools) so that we can set SQL max memory settings accordingly?
hi all,
I need to select collation settings to support chinese characters. As i came to know that i need to select 950 character set. But problem is that i am not able to find the option for chinese characters i.e, 950 character set in the collation settings of the setup. It will be helpful if anyone could suggest me if there is any other option to set while set up or in an existing sql server instance to support chinese character data.
Thanks in advance,
sekhar
Just upgraded a users computer from 7.0 to 2000. All previous SQL Server Registrations are gone IP Addresses, User Names and Passwords arent readily availiable. Are the old connections stored in a DLL or Ini that I can place in the SQL 2000 folder to regain those or do I need to recreate some DSNs?
Help
Matt
Could someone give me an ideal of what it takes to upgrade the memory in my cluster. Is it as easy has upgrading the passive node, switching the nodes, then upgrading the other server. And do I need to re-configure the windows cluster. And will both server need to be down at the same time at any point.
Or is their more to the process? Thanks for any assistance.
btw, I currently have 16mb of RAM in each node.
Hi
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
Thanks
Priyanka
We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2000 Server to a SQL Server 2005 Cluster running on Windows Server 2003. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2000 Server to Windows 2003 Server at the same time. We want to keep downtime to a bare minimum.
What we were thinking was the following steps... Anyone try this?
1. Break the link between the servers.
2. Install a fresh copy of windows 2003 server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2000 Server and SQL Server 2000 serving our customers.
3. Restore a copy of a backup from the active production side to the node we're upgrading and at that point we would bring the active node down, switching the active node to be the newly upgraded server.
4. As a final step, the old active node would now have the link to it broken, we would install a fresh copy of windows 2003 server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.
Thoughts?
Friends -
Need your help and guidence for doing upgrading SQL Server 2000 Cluster to SQL Server 2005 Cluster.
Let me explain my current environment.
1. Currently SQL Server 2000 Cluster environment is running on Windows 2000 Server we need to upgrade this to SQL Server 2005 on Windows 2003 Server. >>> Production environment.
My Plans:
1. On Testing Environment Install SQL Server 2000 cluster on Windows 2003 Server and do a restore of databases from the produciton environment.
2. Upgrade In-Place from SQL Server 2000 Cluster to SQL Server 2005 Cluster.
My doubts
1. Can i install SQL Server 2000 Cluster on Windows 2003 Server. Is it possible or not.
Please advise me and correct my steps.
Cheers
VSH
Hi,
I have setup a SQL 2000 cluster and the cluster works fine. I have given the SQl cluster a virtual IP address too.
I am unable to connect to the Cluster from a remote machine, using SQL Enterprise Manager or SQL Query Analyzer.
But I could connect to it from the machines involved in the CLuster.
Someone plese show some direction on this.
Thanks,
Manoj
I have the following SQL FCI configuration:
NODE1 -256GB
INST1 - 64GB min/64GB max
INST2 - 64GB min/64GB max
NODE2 - 256GB
INST3- 64GB min/64GB max
INST4- 64GB min/64GB max
With this configuration and if all instances are running on the same node there will be enough memory for them to run. Knowing that normally i ll have only 2 instances in each node wouldnt it be better the following config?
NODE1 -256GB
INST1 - 64GB min/128GB max
INST2 - 64GB min/128GB max
NODE2 - 256GB
INST3- 64GB min/128GB max
INST4- 64GB min/128GB max
With this configuration and in case all the instances (due to a failure) start running on only 1 node, SQL will adjust all instances to just use Min memory specified?
Friends -
Could any one of you provide steps for upgrading SQL Server 2000 cluster to SQL server 2005 cluster.
My environment is Windows 2003 server.
Appreciate your support.
Cheers
VSH
Hi everybody,
I have the cluster configuration on Windows2000 Advance Server.
Is it’s possible to install Active–Passive configuration for SQL 7.0 Enterprise edition AND Active–Active configuration for SQL Server 2000 Enterprise edition on the SAME Cluster server? If it's will be work together?
Thank you in advance,
Miriam
I have heard there were problems whne applying sql sp4 to clusters on sql 2000, has anyone encountered this? problem is we are getting
Error: 17883, Severity: 1, State: 0
which the cure is to apply this service pack sql 4 for sql 2000
If I have SQL Server 2000 ENT edition on a cluster active/passive. Do I need to apply the service pack on the active instance, and then bring up other instance and reapply? While I am at it, I believe I still have to apply the hotfix for the awe memory problem also?
View 3 Replies View RelatedI am installing sql cluster on Windows Datacenter, but I get the following error. "setup failed to perform required operations on the cluster nodes".
when I looked at the cluster log file, it has has the following error:
"Installation of the Microsoft Data Access Components package failed. (-1)"
does anyone know how to fix ths error.
Thank You,
john
I am currently running an active/passive cluster NT4 with SQL7 clustered. I am trying to upgrade SQL to a clustered 2000 version. I have a san disk area allocated as a Z: drive. These are the following steps I have taken:
1. Used cluster wizard to uncluster the SQL 7 instance.
2. Using the SQL 2000 Enterprise cd created a new virtual server (with the same IP address and name as the previous SQL 7 server) I have left the install as a default instance.
3. Part way through the install I am asked to browse to the data area. The data area is Z:mssql7data.
On completion of the install I now have the following data path Z:mssql7datamssqldata and all the databases are still SQL7 databases.
My questions are:
How can I get the install to update/convert the databases to SQL 2000 as it doesnt seem to recognise them?
Will the install always suffix the data path with mssqldata
Any assistance appreciated.
Hey there I am an SQL noob, our bank has no real SQL Admin, we had onw that left but never had a good knowledge transfer. We have 2 SQL servers clustered. IBSQL1 and IBSQL2 they make up cluster IBSQL. We have 2 other servers IBIIS1 ans IBIIS2 and we noticed when installing apps on them that they could not see the database. Yet it can ping IBSQL. The problem is on IBSQL1 and 2 port 1433 is not listening or open. Thus on IBSQL it isn't either of course. I have read of all kinds of people having this issue and most have said you need to manually add a connection string "Provider=sqloledb;Data Source=machineName,1433;Initial Catalog=xx;User ID=xx;Password=xx" Problem is I do not know how to add a connection string.
I need step by step instructions since im new to this. Start/programs/etc..... Apparently it is not enough to open the properties of tcp/ip in the config util and say 1433. You need to reenforce it by adding that string.
As it stands I cannot "telnet ibsql 1433" it tells me to take a hike basically.
So is the connection string the likely cause? or could it be something simpler? Thanks in advance.