Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts

Friday, March 30, 2012

Max mirroring DB on a 64-bit sysstem?

According to BOL we know

"On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session."

My question is: What is maximum number of mirrored DB we can deploy on 64-bit system per one sql instance?

It depends. Do you have a 64-CPU box with 1 TB of RAM or a 4 CPU box with 4 GB? The variety of 64-bit hardware available makes specific guidance difficult.

You will need to test your configuration appropriately regardless.

|||

Peter thanks for you answer.

We have got the next configuration :

HP Superdome (1 cell) 4 CPU?(Dual core Itanium 2 1.6GHz, 24 MB Cache),?32GB.

|||

In the case of multiple databases, I use a different tack to overcome the problem. If I find any database failed over, I run SQL commands to fail them all over. "ALTER DATABASE [dbname] SET PARTNER FAILOVER" you must proceed the command with "USE [master]". It works in my case as i am running web code against multiple databases at any given point in time, so I have to have them all failover at the same time anyways. The statement to return the state of each database is "SELECT * from sys.database_mirroring WHERE mirroring_guid IS NOT NULL" I read the values programatically and run the appropriate failover statements depending on the values returned.

|||This was extremely useful, I copied it verbatim into a scheduled task that runs every minute and checks this, because we've had the same issue where only some but not all databases fail over. There should be an SQL Server feature to make multiple databases "atomic" for the purpose of failover.sql

Max mirroring DB on a 64-bit sysstem?

According to BOL we know

"On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session."

My question is: What is maximum number of mirrored DB we can deploy on 64-bit system per one sql instance?

It depends. Do you have a 64-CPU box with 1 TB of RAM or a 4 CPU box with 4 GB? The variety of 64-bit hardware available makes specific guidance difficult.

You will need to test your configuration appropriately regardless.

|||

Peter thanks for you answer.

We have got the next configuration :

HP Superdome (1 cell) 4 CPU?(Dual core Itanium 2 1.6GHz, 24 MB Cache),?32GB.

|||

In the case of multiple databases, I use a different tack to overcome the problem. If I find any database failed over, I run SQL commands to fail them all over. "ALTER DATABASE [dbname] SET PARTNER FAILOVER" you must proceed the command with "USE [master]". It works in my case as i am running web code against multiple databases at any given point in time, so I have to have them all failover at the same time anyways. The statement to return the state of each database is "SELECT * from sys.database_mirroring WHERE mirroring_guid IS NOT NULL" I read the values programatically and run the appropriate failover statements depending on the values returned.

|||This was extremely useful, I copied it verbatim into a scheduled task that runs every minute and checks this, because we've had the same issue where only some but not all databases fail over. There should be an SQL Server feature to make multiple databases "atomic" for the purpose of failover.

Max mirroring DB on a 64-bit sysstem?

According to BOL we know

"On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session."

My question is: What is maximum number of mirrored DB we can deploy on 64-bit system per one sql instance?

It depends. Do you have a 64-CPU box with 1 TB of RAM or a 4 CPU box with 4 GB? The variety of 64-bit hardware available makes specific guidance difficult.

You will need to test your configuration appropriately regardless.

|||

Peter thanks for you answer.

We have got the next configuration :

HP Superdome (1 cell) 4 CPU?(Dual core Itanium 2 1.6GHz, 24 MB Cache),?32GB.

|||

In the case of multiple databases, I use a different tack to overcome the problem. If I find any database failed over, I run SQL commands to fail them all over. "ALTER DATABASE [dbname] SET PARTNER FAILOVER" you must proceed the command with "USE [master]". It works in my case as i am running web code against multiple databases at any given point in time, so I have to have them all failover at the same time anyways. The statement to return the state of each database is "SELECT * from sys.database_mirroring WHERE mirroring_guid IS NOT NULL" I read the values programatically and run the appropriate failover statements depending on the values returned.

|||This was extremely useful, I copied it verbatim into a scheduled task that runs every minute and checks this, because we've had the same issue where only some but not all databases fail over. There should be an SQL Server feature to make multiple databases "atomic" for the purpose of failover.

max memory setting and /3GB switch

A server with 8 GB RAM and MS SQL only server with one instance, /PAE and AWE
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.
YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.

max memory setting and /3GB switch

A server with 8 GB RAM and MS SQL only server with one instance, /PAE and AW
E
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.

max memory setting and /3GB switch

A server with 8 GB RAM and MS SQL only server with one instance, /PAE and AWE
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
--
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.

Max Memory per SQL instance

Hi
I have a SQL 7 and SQL 2000 instance on one server with a
total of 2Gb of physical ram.
Currently all the physical ram is used up by both
instances causing SQL to run slowly.
My question is: Is the 2Gb ram limit a server limit or a
instance limit ie if I add an additional 2 Gb of ram will
each instance use a maximum of 2Gb each or will the
additional ram be a waste of time?
Thanks in advance.
DarylThanks for your reply. You have confirmed what I believe
is the case so I will proceed and add the additional
memory. For your reference the OS is Win Server 2000 and
the SQL 2000 instance is the standard edition.
Thanks again.
Daryl
>--Original Message--
>It depends on the OS and edition of sql server you have
as to how much ram
>they can access but yes, each instance should be able to
use 2GB.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"dazza" <darylg@.intergen.co.nz> wrote in message
>news:018001c35228$af340fa0$7d02280a@.phx.gbl...
>> Hi
>> I have a SQL 7 and SQL 2000 instance on one server with
a
>> total of 2Gb of physical ram.
>> Currently all the physical ram is used up by both
>> instances causing SQL to run slowly.
>> My question is: Is the 2Gb ram limit a server limit or a
>> instance limit ie if I add an additional 2 Gb of ram
will
>> each instance use a maximum of 2Gb each or will the
>> additional ram be a waste of time?
>> Thanks in advance.
>> Daryl
>
>.
>sql

Max Memory for each standard instance

If I have multiple standard instances installed, can I assign fixed memory to
each instance?
Like 2GB of fixed memory for each Standard instance.
In other words when microsoft says that the Max mem for standard edition is
2GB, do they mean for each standard instance, or for standard multiple
instances?
That's for each instance.
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D04974BB-B43E-495A-A86A-B5DABB498A23@.microsoft.com...
> If I have multiple standard instances installed, can I assign fixed memory
> to
> each instance?
> Like 2GB of fixed memory for each Standard instance.
> In other words when microsoft says that the Max mem for standard edition
> is
> 2GB, do they mean for each standard instance, or for standard multiple
> instances?
>

Max Memory for each standard instance

If I have multiple standard instances installed, can I assign fixed memory to
each instance?
Like 2GB of fixed memory for each Standard instance.
In other words when microsoft says that the Max mem for standard edition is
2GB, do they mean for each standard instance, or for standard multiple
instances?That's for each instance.
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D04974BB-B43E-495A-A86A-B5DABB498A23@.microsoft.com...
> If I have multiple standard instances installed, can I assign fixed memory
> to
> each instance?
> Like 2GB of fixed memory for each Standard instance.
> In other words when microsoft says that the Max mem for standard edition
> is
> 2GB, do they mean for each standard instance, or for standard multiple
> instances?
>sql

Max Memory for each standard instance

If I have multiple standard instances installed, can I assign fixed memory t
o
each instance?
Like 2GB of fixed memory for each Standard instance.
In other words when microsoft says that the Max mem for standard edition is
2GB, do they mean for each standard instance, or for standard multiple
instances?That's for each instance.
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D04974BB-B43E-495A-A86A-B5DABB498A23@.microsoft.com...
> If I have multiple standard instances installed, can I assign fixed memory
> to
> each instance?
> Like 2GB of fixed memory for each Standard instance.
> In other words when microsoft says that the Max mem for standard edition
> is
> 2GB, do they mean for each standard instance, or for standard multiple
> instances?
>