Help with Cluster

I inherited a SQL 2000 Enterprise active/passive cluster under AS. Both
nodes run on identical HP DL580's with 4 Xeon processors and 8GB memory.
The former dba attempted to setup AWE memory but missed a couple of fine
points. The first thing missed: the boot.ini only has the /PAE and no /3GB
switch. The second thing missed: sp_configure 'awe enabled' is set to 0.
As a result, SQL gets only 2GB of virtual memory and 0GB of PAE memory.

My question is how do I fix this on an active/passive cluster. Here is what
I think. Add the /3GB switch to the passive node and reboot the passive
node. Now it seems I am in trouble. If I pause the active node, I cause
the passive node to wake up, but the memory images do not match. If I start
with the active node and add the /3GB switch and reboot, I fail-over to the
passive. Everything is still OK, the memory matches until I reboot, then
the memory maps do not match.

So, do I break the cluster, install the /3GB switch on each box and then
recreate the cluster? Isn't there an easier way?

Also, can I enable AWE before I begin the /3GB shuffle. I seems I could
avoid a second dance.

Please help.

GaryGary,

I can answer one of your questions.

> Also, can I enable AWE before I begin the /3GB shuffle. I seems I could
> avoid a second dance.

You do not need to use the "/3GB" switch in order to use AWE memory.
You may find an explanation of each switch helpful:

The "/PAE" switch allows Windows to use memory above the 4GB native
limit. So this switch is needed to enable Windows to address memory
above 4GB. (Additionally, you must use a Server Operating System that
supports more than 4GB. You're using Windows 2000 Advanced Server,
which allows up to 8GB to be addressed.)

The "/3GB" switch allows Windows applications to use 3GB out of the
4GB Windows supports natively thereby leaving 1GB of memory for the
Windows OS. By default (i.e., not using the "/3GB" switch),
applications will only use 2GB out of the first 4GB on the server and
leave 2GB for the Windows OS.

You have a total of 8GB of RAM. If you don't use the "/3GB" switch,
then SQL Server will still have 6GB of RAM available to use. You may
prefer to leave the 2GB available to the OS rather than take it for
SQL Server. But at you stated, you must set the "awe enabled" option
from "0" to "1" and restart the SQL Server service. In case you are
not aware, AWE does not dynamically manage memory like SQL Server does
by default, so if you have more than one instance of SQL Server
running on each node in the cluster, you may not want to use AWE
memory.

You may find the following link helpful:

AWE Memory SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/awe_memory.asp

HOW TO: Configure Memory for More Than 2 GB in SQL Server
http://support.microsoft.com/defaul...KB;EN-US;274750

Large Memory Support Is Available in Windows 2000 and Windows Server
2003
http://support.microsoft.com/defaul...b;EN-US;q283037

SQL Server Books Online Topics "Managing AWE Support", "Effects of min
and max server memory"; "Memory Architecture"; "Memory Architecture";
"Server Memory Options"; "SQL Server Memory Pool"

I hope you find this information helpful.

Regards,

Matt Patterson
HP Services
Business Critical Enterprise Team
Microsoft Database Supportsql

Help with a SQL Server 2000 issue

Hi,

I signed up with a hosting service (brinkster.com) – they support SQL Server 2000.

I connected to the remote database using Enterprise Manager without any issues.

I also found an article by Scott Guthrie on how to change the providers to use SQL 2000 instead of SQL Express,(http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx),

but when I got to the part where I have to select a sever and a database (step 3 of the wizard), I got the following message

"Query database list failed"

"Failed to query a list of databse name from the SQL server.

Invalid object name 'sysdatabases'."

I ran the wizard on a local SQL Server and it worked fine!

Is there a workaround?

Please help… Thanks.

When using the wizard, make sure the account used to connect to the SQL2000 has select permission on the sysdatabases, and the default database of the account is master.|||

Thanks, I have been trying to figure out how to make sure the account has select permission on the sysdatabases, and that the default database is master.

I tried connecting to the master database and query the users table, I got an error message saying "Login failed for user 'user'"

It would be great if you can help out some more - point to more documentations/articles.

Again, Thaks.

|||

samirayes:

I tried connecting to the master database and query the users table, I got an error message saying "Login failed for user 'user'"

OK it seems to be a permission issue. What are you using to connect? You can easily check the permissions in Enterprise Manager: connect to the SQL instance-> go to Security->Logins->check the property of the 'user' login (if it is not there, create it)

|||

Brilliant - lori_jay you are fantastic. Thank you.

On the user's properties, I changed the default database to master (it was set to the database set up by the hosting service for the account)

I ran the wizard and voila - it worked like it should.

Do I have to change the default database to what it was, or should I keep it set to Master?

Again, thank you very much.

|||

samirayes:

Do I have to change the default database to what it was, or should I keep it set to Master?

In most scenarios, we can just set the default database to any database we need for the login--this option is just a part of security (login) setting. So there is no need to keep default database as Master, in this case we just set it to master to get the aspnet_regsql utility work Actually the aspnet_regsql could be better: if it retrieve database information from 'master..sysdatabases' not just 'sysdatabases', we do not have to change the default database for the login used in the wizard.

Help to verify ?

I do a SQL migration. Help to verify whether it's OK.
Old Server: SVR1, SQL Server Enterprise edition
New Server: SVR2, SQL Server Enterprise Edition
There are many user databases and linked server in SVR1,
it's inconvenient to copy database file and attach to new
server. And also need to create user account, etc. Because
all user database registeration info are stored in master
database, so I try to modify master database. Following is
the procedure:
1.At SVR1, set using SQL authentication, stop SQL server.
copy all system and user database to SVR2, save at
c:\olddata
2.At SVR2, attach master.mdf and mastlog.ldf in c:\olddata
as masternew.
3.In three table sysdatabases, sysaltfiles, sysdevices of
masternew database, modify old path to the real path
c:\sqldata.
4.At SVR2, stop all SQL service, copy all file from
c:\oldpath to c:\sqldata, select yes if need to confirm
file replace
5.At SVR2, start SQL Service, use SQL Query analyzer, run
following script:
exec sp_dropserver 'SVR1'
go
exec sp_addserver 'SVR2','local'
go
6.Open SQL Server Enterprise Manager, it seems all is ok.
Is there any problem to do like this? Or I need to do any
more? Your message to me is my pleasure.Hi
The following article describes how to transfer over to a new server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
If you followed the steps then there would be no need copy the master
database, but the following article details how to:
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
Your process does not seem to transfer any jobs
John
"ronggui" <ronggui999@.hotmail.com> wrote in message
news:001601c34967$c1a43850$a601280a@.phx.gbl...
> I do a SQL migration. Help to verify whether it's OK.
> Old Server: SVR1, SQL Server Enterprise edition
> New Server: SVR2, SQL Server Enterprise Edition
> There are many user databases and linked server in SVR1,
> it's inconvenient to copy database file and attach to new
> server. And also need to create user account, etc. Because
> all user database registeration info are stored in master
> database, so I try to modify master database. Following is
> the procedure:
> 1.At SVR1, set using SQL authentication, stop SQL server.
> copy all system and user database to SVR2, save at
> c:\olddata
> 2.At SVR2, attach master.mdf and mastlog.ldf in c:\olddata
> as masternew.
> 3.In three table sysdatabases, sysaltfiles, sysdevices of
> masternew database, modify old path to the real path
> c:\sqldata.
> 4.At SVR2, stop all SQL service, copy all file from
> c:\oldpath to c:\sqldata, select yes if need to confirm
> file replace
> 5.At SVR2, start SQL Service, use SQL Query analyzer, run
> following script:
> exec sp_dropserver 'SVR1'
> go
> exec sp_addserver 'SVR2','local'
> go
> 6.Open SQL Server Enterprise Manager, it seems all is ok.
> Is there any problem to do like this? Or I need to do any
> more? Your message to me is my pleasure.
>|||Hi,
Because in new server SVR2, there is already a master
database. I want to change all the old paths in the master
database from old server SVR1, so I attached as masternew.
By the way, at old server SVR1 system and user databases
are saved in different path, I need changing path in
master, so it can work in new server SVR2.
After changing path, I stop SQL Server SVR2, and copy all
old files to exact location.
>--Original Message--
>You attached the master database as "Masternew". I
>thought the master database must be called "Master".
>I will only copy all the databases to the exact location
>in SVR2 including "Master", "Msdb" etc as SVR1.
>.
>|||Hi
The articles do show how to move the master database, you should follow that
procedure.
John
"ronggui" <ronggui999@.hotmail.com> wrote in message
news:040b01c349b0$cab4bca0$a301280a@.phx.gbl...
> Hi,
> Because in new server SVR2, there is already a master
> database. I want to change all the old paths in the master
> database from old server SVR1, so I attached as masternew.
> By the way, at old server SVR1 system and user databases
> are saved in different path, I need changing path in
> master, so it can work in new server SVR2.
> After changing path, I stop SQL Server SVR2, and copy all
> old files to exact location.
> >--Original Message--
> >You attached the master database as "Masternew". I
> >thought the master database must be called "Master".
> >
> >I will only copy all the databases to the exact location
> >in SVR2 including "Master", "Msdb" etc as SVR1.
> >.
> >