Hi,
I have a few questions regarding sql server 2005 .
Hardware will be running with a single dual core 64 bit Opteron 175.
1) Are there any performance advantages running sql 2005 Workgroup Edition
on Windows 2003 64 bit Edition? If so what are they?
2) Are there any benchmarks on how much faster sql 2005 Standard Edition vs
sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
3) For sql 2005 Standard Edition and the database mirroring functionality :
is it possible to mirror multiple sql servers to just one failover mirror or
do you need a separate sql box to mirror each one ?
Thanks"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
>
Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM, and
lacks the tuning tools of SQL Server.
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
No. For small workloads there shouldn't be much difference.
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
>
One mirror per database, although the mirror server can mirror multiple
databases.
David|||Thanks David,
So for 1) i'm not sure you've answered the question. I know Workgroup
Edition is 32bit but it can run on Windows 2003 Standard 64bit edition using
WOW. Now because i have a 64bit Opteron will i see SQL run quicker in this
config as opposed to using Windows 2003 Standard 32bit Server? Also are
there any performance penalties using WOW?
For 3) i take it you mean you can only have one prinicipal server per
mirror?
So if i have two SQL server machines running different databases and i need
to use just 1 backup server incase one machine fails what are my best
options?
Log-Shipping? or Replication?
Clustering would be too expensive for us and Mirroring would also be too
expensive if you need one mirror for each SQL server machine.
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
> and lacks the tuning tools of SQL Server.
>
> No. For small workloads there shouldn't be much difference.
>
> One mirror per database, although the mirror server can mirror multiple
> databases.
> David
>|||Most of the benefits of 64 bit architectures are a result of the flat memory
space but Workgroup Edition is not going to be able to take advantage of
most of that. running a 32 bit edition in WOW allows 4 GB of user space so
there is some advantage there. Currently, 64 bit processors are generally
slower than the fastest 32 bit processors so in some cases a 32 bit
processor might actually give better performance than a 64 bit processor and
WOW. Of course, with a 64 bit machine you have the advantage of being able
to upgrade to Standard edition and take advantage of the flat memory space
if you run out of horsepower in WOW.
You can have multiple servers mirrored to a single DB Mirroring server
because individual databases are mirrored. You would have to be careful to
ensure that the mirror has enough resources to both handle the load of
mirroring databases from several server and the load of running the
databases when they're failed over.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
> Thanks David,
> So for 1) i'm not sure you've answered the question. I know Workgroup
> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
> in this config as opposed to using Windows 2003 Standard 32bit Server?
> Also are there any performance penalties using WOW?
> For 3) i take it you mean you can only have one prinicipal server per
> mirror?
> So if i have two SQL server machines running different databases and i
> need to use just 1 backup server incase one machine fails what are my best
> options?
> Log-Shipping? or Replication?
> Clustering would be too expensive for us and Mirroring would also be too
> expensive if you need one mirror for each SQL server machine.
> Thanks.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>|||Ok great.
Is there anything required to activate WOW on windows server or does it
automatically just work when you install the Workgroup Edition?
Also can you just confirm that the following scenario is doable:
Two SQL machines with copy of SQL 2005 Standard Edition
named SQL1 and SQL2 (both with there own separate internal ip address)
SQL1 has two databases called DB1 and DB2
SQL2 has two databases called DB3 and DB4
Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the Mirror
Witness Server (WIT1)
DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
Witness Server
Now if both SQL1 and SQ2 fail at the same time can the Mirror take over all
the connections to these servers?
Thanks.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
> Most of the benefits of 64 bit architectures are a result of the flat
> memory space but Workgroup Edition is not going to be able to take
> advantage of most of that. running a 32 bit edition in WOW allows 4 GB of
> user space so there is some advantage there. Currently, 64 bit processors
> are generally slower than the fastest 32 bit processors so in some cases a
> 32 bit processor might actually give better performance than a 64 bit
> processor and WOW. Of course, with a 64 bit machine you have the
> advantage of being able to upgrade to Standard edition and take advantage
> of the flat memory space if you run out of horsepower in WOW.
> You can have multiple servers mirrored to a single DB Mirroring server
> because individual databases are mirrored. You would have to be careful
> to ensure that the mirror has enough resources to both handle the load of
> mirroring databases from several server and the load of running the
> databases when they're failed over.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>|||WOW is always there. Any 32 bit software you install will run in the WOW.
Theoretically, your failover scenario will work if you keep in mind that the
mirror server will have to handle four databases in this case so unless this
server has significantly more power than the primary servers, performance
will suffer if you fail over all the databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:OmGebPlGGHA.3448@.TK2MSFTNGP10.phx.gbl...
> Ok great.
> Is there anything required to activate WOW on windows server or does it
> automatically just work when you install the Workgroup Edition?
> Also can you just confirm that the following scenario is doable:
> Two SQL machines with copy of SQL 2005 Standard Edition
> named SQL1 and SQL2 (both with there own separate internal ip address)
> SQL1 has two databases called DB1 and DB2
> SQL2 has two databases called DB3 and DB4
> Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the
> Mirror
> Witness Server (WIT1)
> DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
> Witness Server
> Now if both SQL1 and SQ2 fail at the same time can the Mirror take over
> all the connections to these servers?
> Thanks.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
>|||1. Each principal can have exactly 1 mirror. A witness can service as many
principal/mirror pairs as you want it to. Mirroring occurs at a database
level, so you can have an instance of SQL Server where there are multiple
databases on that instance in the role of a mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
> Thanks
>sql
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
Mirroring and 64 vs 32 bit performance
Hi,
I have a few questions regarding sql server 2005 .
Hardware will be running with a single dual core 64 bit Opteron 175.
1) Are there any performance advantages running sql 2005 Workgroup Edition
on Windows 2003 64 bit Edition? If so what are they?
2) Are there any benchmarks on how much faster sql 2005 Standard Edition vs
sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
3) For sql 2005 Standard Edition and the database mirroring functionality :
is it possible to mirror multiple sql servers to just one failover mirror or
do you need a separate sql box to mirror each one ?
Thanks
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
>
Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM, and
lacks the tuning tools of SQL Server.
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
No. For small workloads there shouldn't be much difference.
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
>
One mirror per database, although the mirror server can mirror multiple
databases.
David
|||Thanks David,
So for 1) i'm not sure you've answered the question. I know Workgroup
Edition is 32bit but it can run on Windows 2003 Standard 64bit edition using
WOW. Now because i have a 64bit Opteron will i see SQL run quicker in this
config as opposed to using Windows 2003 Standard 32bit Server? Also are
there any performance penalties using WOW?
For 3) i take it you mean you can only have one prinicipal server per
mirror?
So if i have two SQL server machines running different databases and i need
to use just 1 backup server incase one machine fails what are my best
options?
Log-Shipping? or Replication?
Clustering would be too expensive for us and Mirroring would also be too
expensive if you need one mirror for each SQL server machine.
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
> and lacks the tuning tools of SQL Server.
>
> No. For small workloads there shouldn't be much difference.
>
> One mirror per database, although the mirror server can mirror multiple
> databases.
> David
>
|||Most of the benefits of 64 bit architectures are a result of the flat memory
space but Workgroup Edition is not going to be able to take advantage of
most of that. running a 32 bit edition in WOW allows 4 GB of user space so
there is some advantage there. Currently, 64 bit processors are generally
slower than the fastest 32 bit processors so in some cases a 32 bit
processor might actually give better performance than a 64 bit processor and
WOW. Of course, with a 64 bit machine you have the advantage of being able
to upgrade to Standard edition and take advantage of the flat memory space
if you run out of horsepower in WOW.
You can have multiple servers mirrored to a single DB Mirroring server
because individual databases are mirrored. You would have to be careful to
ensure that the mirror has enough resources to both handle the load of
mirroring databases from several server and the load of running the
databases when they're failed over.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
> Thanks David,
> So for 1) i'm not sure you've answered the question. I know Workgroup
> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
> in this config as opposed to using Windows 2003 Standard 32bit Server?
> Also are there any performance penalties using WOW?
> For 3) i take it you mean you can only have one prinicipal server per
> mirror?
> So if i have two SQL server machines running different databases and i
> need to use just 1 backup server incase one machine fails what are my best
> options?
> Log-Shipping? or Replication?
> Clustering would be too expensive for us and Mirroring would also be too
> expensive if you need one mirror for each SQL server machine.
> Thanks.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>
|||Ok great.
Is there anything required to activate WOW on windows server or does it
automatically just work when you install the Workgroup Edition?
Also can you just confirm that the following scenario is doable:
Two SQL machines with copy of SQL 2005 Standard Edition
named SQL1 and SQL2 (both with there own separate internal ip address)
SQL1 has two databases called DB1 and DB2
SQL2 has two databases called DB3 and DB4
Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the Mirror
Witness Server (WIT1)
DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
Witness Server
Now if both SQL1 and SQ2 fail at the same time can the Mirror take over all
the connections to these servers?
Thanks.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
> Most of the benefits of 64 bit architectures are a result of the flat
> memory space but Workgroup Edition is not going to be able to take
> advantage of most of that. running a 32 bit edition in WOW allows 4 GB of
> user space so there is some advantage there. Currently, 64 bit processors
> are generally slower than the fastest 32 bit processors so in some cases a
> 32 bit processor might actually give better performance than a 64 bit
> processor and WOW. Of course, with a 64 bit machine you have the
> advantage of being able to upgrade to Standard edition and take advantage
> of the flat memory space if you run out of horsepower in WOW.
> You can have multiple servers mirrored to a single DB Mirroring server
> because individual databases are mirrored. You would have to be careful
> to ensure that the mirror has enough resources to both handle the load of
> mirroring databases from several server and the load of running the
> databases when they're failed over.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>
|||WOW is always there. Any 32 bit software you install will run in the WOW.
Theoretically, your failover scenario will work if you keep in mind that the
mirror server will have to handle four databases in this case so unless this
server has significantly more power than the primary servers, performance
will suffer if you fail over all the databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:OmGebPlGGHA.3448@.TK2MSFTNGP10.phx.gbl...
> Ok great.
> Is there anything required to activate WOW on windows server or does it
> automatically just work when you install the Workgroup Edition?
> Also can you just confirm that the following scenario is doable:
> Two SQL machines with copy of SQL 2005 Standard Edition
> named SQL1 and SQL2 (both with there own separate internal ip address)
> SQL1 has two databases called DB1 and DB2
> SQL2 has two databases called DB3 and DB4
> Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the
> Mirror
> Witness Server (WIT1)
> DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
> Witness Server
> Now if both SQL1 and SQ2 fail at the same time can the Mirror take over
> all the connections to these servers?
> Thanks.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
>
|||1. Each principal can have exactly 1 mirror. A witness can service as many
principal/mirror pairs as you want it to. Mirroring occurs at a database
level, so you can have an instance of SQL Server where there are multiple
databases on that instance in the role of a mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
> Thanks
>
I have a few questions regarding sql server 2005 .
Hardware will be running with a single dual core 64 bit Opteron 175.
1) Are there any performance advantages running sql 2005 Workgroup Edition
on Windows 2003 64 bit Edition? If so what are they?
2) Are there any benchmarks on how much faster sql 2005 Standard Edition vs
sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
3) For sql 2005 Standard Edition and the database mirroring functionality :
is it possible to mirror multiple sql servers to just one failover mirror or
do you need a separate sql box to mirror each one ?
Thanks
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
>
Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM, and
lacks the tuning tools of SQL Server.
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
No. For small workloads there shouldn't be much difference.
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
>
One mirror per database, although the mirror server can mirror multiple
databases.
David
|||Thanks David,
So for 1) i'm not sure you've answered the question. I know Workgroup
Edition is 32bit but it can run on Windows 2003 Standard 64bit edition using
WOW. Now because i have a 64bit Opteron will i see SQL run quicker in this
config as opposed to using Windows 2003 Standard 32bit Server? Also are
there any performance penalties using WOW?
For 3) i take it you mean you can only have one prinicipal server per
mirror?
So if i have two SQL server machines running different databases and i need
to use just 1 backup server incase one machine fails what are my best
options?
Log-Shipping? or Replication?
Clustering would be too expensive for us and Mirroring would also be too
expensive if you need one mirror for each SQL server machine.
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
> and lacks the tuning tools of SQL Server.
>
> No. For small workloads there shouldn't be much difference.
>
> One mirror per database, although the mirror server can mirror multiple
> databases.
> David
>
|||Most of the benefits of 64 bit architectures are a result of the flat memory
space but Workgroup Edition is not going to be able to take advantage of
most of that. running a 32 bit edition in WOW allows 4 GB of user space so
there is some advantage there. Currently, 64 bit processors are generally
slower than the fastest 32 bit processors so in some cases a 32 bit
processor might actually give better performance than a 64 bit processor and
WOW. Of course, with a 64 bit machine you have the advantage of being able
to upgrade to Standard edition and take advantage of the flat memory space
if you run out of horsepower in WOW.
You can have multiple servers mirrored to a single DB Mirroring server
because individual databases are mirrored. You would have to be careful to
ensure that the mirror has enough resources to both handle the load of
mirroring databases from several server and the load of running the
databases when they're failed over.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
> Thanks David,
> So for 1) i'm not sure you've answered the question. I know Workgroup
> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
> in this config as opposed to using Windows 2003 Standard 32bit Server?
> Also are there any performance penalties using WOW?
> For 3) i take it you mean you can only have one prinicipal server per
> mirror?
> So if i have two SQL server machines running different databases and i
> need to use just 1 backup server incase one machine fails what are my best
> options?
> Log-Shipping? or Replication?
> Clustering would be too expensive for us and Mirroring would also be too
> expensive if you need one mirror for each SQL server machine.
> Thanks.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>
|||Ok great.
Is there anything required to activate WOW on windows server or does it
automatically just work when you install the Workgroup Edition?
Also can you just confirm that the following scenario is doable:
Two SQL machines with copy of SQL 2005 Standard Edition
named SQL1 and SQL2 (both with there own separate internal ip address)
SQL1 has two databases called DB1 and DB2
SQL2 has two databases called DB3 and DB4
Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the Mirror
Witness Server (WIT1)
DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
Witness Server
Now if both SQL1 and SQ2 fail at the same time can the Mirror take over all
the connections to these servers?
Thanks.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
> Most of the benefits of 64 bit architectures are a result of the flat
> memory space but Workgroup Edition is not going to be able to take
> advantage of most of that. running a 32 bit edition in WOW allows 4 GB of
> user space so there is some advantage there. Currently, 64 bit processors
> are generally slower than the fastest 32 bit processors so in some cases a
> 32 bit processor might actually give better performance than a 64 bit
> processor and WOW. Of course, with a 64 bit machine you have the
> advantage of being able to upgrade to Standard edition and take advantage
> of the flat memory space if you run out of horsepower in WOW.
> You can have multiple servers mirrored to a single DB Mirroring server
> because individual databases are mirrored. You would have to be careful
> to ensure that the mirror has enough resources to both handle the load of
> mirroring databases from several server and the load of running the
> databases when they're failed over.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>
|||WOW is always there. Any 32 bit software you install will run in the WOW.
Theoretically, your failover scenario will work if you keep in mind that the
mirror server will have to handle four databases in this case so unless this
server has significantly more power than the primary servers, performance
will suffer if you fail over all the databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:OmGebPlGGHA.3448@.TK2MSFTNGP10.phx.gbl...
> Ok great.
> Is there anything required to activate WOW on windows server or does it
> automatically just work when you install the Workgroup Edition?
> Also can you just confirm that the following scenario is doable:
> Two SQL machines with copy of SQL 2005 Standard Edition
> named SQL1 and SQL2 (both with there own separate internal ip address)
> SQL1 has two databases called DB1 and DB2
> SQL2 has two databases called DB3 and DB4
> Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the
> Mirror
> Witness Server (WIT1)
> DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
> Witness Server
> Now if both SQL1 and SQ2 fail at the same time can the Mirror take over
> all the connections to these servers?
> Thanks.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
>
|||1. Each principal can have exactly 1 mirror. A witness can service as many
principal/mirror pairs as you want it to. Mirroring occurs at a database
level, so you can have an instance of SQL Server where there are multiple
databases on that instance in the role of a mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
> Thanks
>
Mirroring and 64 vs 32 bit performance
Hi,
I have a few questions regarding sql server 2005 .
Hardware will be running with a single dual core 64 bit Opteron 175.
1) Are there any performance advantages running sql 2005 Workgroup Edition
on Windows 2003 64 bit Edition? If so what are they?
2) Are there any benchmarks on how much faster sql 2005 Standard Edition vs
sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
3) For sql 2005 Standard Edition and the database mirroring functionality :
is it possible to mirror multiple sql servers to just one failover mirror or
do you need a separate sql box to mirror each one ?
Thanks"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
>
Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM, and
lacks the tuning tools of SQL Server.
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
No. For small workloads there shouldn't be much difference.
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
>
One mirror per database, although the mirror server can mirror multiple
databases.
David|||Thanks David,
So for 1) i'm not sure you've answered the question. I know Workgroup
Edition is 32bit but it can run on Windows 2003 Standard 64bit edition using
WOW. Now because i have a 64bit Opteron will i see SQL run quicker in this
config as opposed to using Windows 2003 Standard 32bit Server? Also are
there any performance penalties using WOW?
For 3) i take it you mean you can only have one prinicipal server per
mirror?
So if i have two SQL server machines running different databases and i need
to use just 1 backup server incase one machine fails what are my best
options?
Log-Shipping? or Replication?
Clustering would be too expensive for us and Mirroring would also be too
expensive if you need one mirror for each SQL server machine.
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
> and lacks the tuning tools of SQL Server.
>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
>> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring functionality
>> : is it possible to mirror multiple sql servers to just one failover
>> mirror or do you need a separate sql box to mirror each one ?
> One mirror per database, although the mirror server can mirror multiple
> databases.
> David
>|||Most of the benefits of 64 bit architectures are a result of the flat memory
space but Workgroup Edition is not going to be able to take advantage of
most of that. running a 32 bit edition in WOW allows 4 GB of user space so
there is some advantage there. Currently, 64 bit processors are generally
slower than the fastest 32 bit processors so in some cases a 32 bit
processor might actually give better performance than a 64 bit processor and
WOW. Of course, with a 64 bit machine you have the advantage of being able
to upgrade to Standard edition and take advantage of the flat memory space
if you run out of horsepower in WOW.
You can have multiple servers mirrored to a single DB Mirroring server
because individual databases are mirrored. You would have to be careful to
ensure that the mirror has enough resources to both handle the load of
mirroring databases from several server and the load of running the
databases when they're failed over.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
> Thanks David,
> So for 1) i'm not sure you've answered the question. I know Workgroup
> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
> in this config as opposed to using Windows 2003 Standard 32bit Server?
> Also are there any performance penalties using WOW?
> For 3) i take it you mean you can only have one prinicipal server per
> mirror?
> So if i have two SQL server machines running different databases and i
> need to use just 1 backup server incase one machine fails what are my best
> options?
> Log-Shipping? or Replication?
> Clustering would be too expensive for us and Mirroring would also be too
> expensive if you need one mirror for each SQL server machine.
> Thanks.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
>> and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
>> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each one
>> ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>|||Ok great.
Is there anything required to activate WOW on windows server or does it
automatically just work when you install the Workgroup Edition?
Also can you just confirm that the following scenario is doable:
Two SQL machines with copy of SQL 2005 Standard Edition
named SQL1 and SQL2 (both with there own separate internal ip address)
SQL1 has two databases called DB1 and DB2
SQL2 has two databases called DB3 and DB4
Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the Mirror
Witness Server (WIT1)
DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
Witness Server
Now if both SQL1 and SQ2 fail at the same time can the Mirror take over all
the connections to these servers?
Thanks.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
> Most of the benefits of 64 bit architectures are a result of the flat
> memory space but Workgroup Edition is not going to be able to take
> advantage of most of that. running a 32 bit edition in WOW allows 4 GB of
> user space so there is some advantage there. Currently, 64 bit processors
> are generally slower than the fastest 32 bit processors so in some cases a
> 32 bit processor might actually give better performance than a 64 bit
> processor and WOW. Of course, with a 64 bit machine you have the
> advantage of being able to upgrade to Standard edition and take advantage
> of the flat memory space if you run out of horsepower in WOW.
> You can have multiple servers mirrored to a single DB Mirroring server
> because individual databases are mirrored. You would have to be careful
> to ensure that the mirror has enough resources to both handle the load of
> mirroring databases from several server and the load of running the
> databases when they're failed over.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>> Thanks David,
>> So for 1) i'm not sure you've answered the question. I know Workgroup
>> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
>> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
>> in this config as opposed to using Windows 2003 Standard 32bit Server?
>> Also are there any performance penalties using WOW?
>> For 3) i take it you mean you can only have one prinicipal server per
>> mirror?
>> So if i have two SQL server machines running different databases and i
>> need to use just 1 backup server incase one machine fails what are my
>> best options?
>> Log-Shipping? or Replication?
>> Clustering would be too expensive for us and Mirroring would also be too
>> expensive if you need one mirror for each SQL server machine.
>> Thanks.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
>> and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard
>> Edition vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit
>> Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each
>> one ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>>
>|||WOW is always there. Any 32 bit software you install will run in the WOW.
Theoretically, your failover scenario will work if you keep in mind that the
mirror server will have to handle four databases in this case so unless this
server has significantly more power than the primary servers, performance
will suffer if you fail over all the databases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:OmGebPlGGHA.3448@.TK2MSFTNGP10.phx.gbl...
> Ok great.
> Is there anything required to activate WOW on windows server or does it
> automatically just work when you install the Workgroup Edition?
> Also can you just confirm that the following scenario is doable:
> Two SQL machines with copy of SQL 2005 Standard Edition
> named SQL1 and SQL2 (both with there own separate internal ip address)
> SQL1 has two databases called DB1 and DB2
> SQL2 has two databases called DB3 and DB4
> Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the
> Mirror
> Witness Server (WIT1)
> DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
> Witness Server
> Now if both SQL1 and SQ2 fail at the same time can the Mirror take over
> all the connections to these servers?
> Thanks.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Most of the benefits of 64 bit architectures are a result of the flat
>> memory space but Workgroup Edition is not going to be able to take
>> advantage of most of that. running a 32 bit edition in WOW allows 4 GB
>> of user space so there is some advantage there. Currently, 64 bit
>> processors are generally slower than the fastest 32 bit processors so in
>> some cases a 32 bit processor might actually give better performance than
>> a 64 bit processor and WOW. Of course, with a 64 bit machine you have
>> the advantage of being able to upgrade to Standard edition and take
>> advantage of the flat memory space if you run out of horsepower in WOW.
>> You can have multiple servers mirrored to a single DB Mirroring server
>> because individual databases are mirrored. You would have to be careful
>> to ensure that the mirror has enough resources to both handle the load of
>> mirroring databases from several server and the load of running the
>> databases when they're failed over.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>> Thanks David,
>> So for 1) i'm not sure you've answered the question. I know Workgroup
>> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
>> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
>> in this config as opposed to using Windows 2003 Standard 32bit Server?
>> Also are there any performance penalties using WOW?
>> For 3) i take it you mean you can only have one prinicipal server per
>> mirror?
>> So if i have two SQL server machines running different databases and i
>> need to use just 1 backup server incase one machine fails what are my
>> best options?
>> Log-Shipping? or Replication?
>> Clustering would be too expensive for us and Mirroring would also be too
>> expensive if you need one mirror for each SQL server machine.
>> Thanks.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of
>> RAM, and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard
>> Edition vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit
>> Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each
>> one ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>>
>>
>|||1. Each principal can have exactly 1 mirror. A witness can service as many
principal/mirror pairs as you want it to. Mirroring occurs at a database
level, so you can have an instance of SQL Server where there are multiple
databases on that instance in the role of a mirror.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
> Thanks
>
I have a few questions regarding sql server 2005 .
Hardware will be running with a single dual core 64 bit Opteron 175.
1) Are there any performance advantages running sql 2005 Workgroup Edition
on Windows 2003 64 bit Edition? If so what are they?
2) Are there any benchmarks on how much faster sql 2005 Standard Edition vs
sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
3) For sql 2005 Standard Edition and the database mirroring functionality :
is it possible to mirror multiple sql servers to just one failover mirror or
do you need a separate sql box to mirror each one ?
Thanks"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
>
Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM, and
lacks the tuning tools of SQL Server.
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
No. For small workloads there shouldn't be much difference.
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
>
One mirror per database, although the mirror server can mirror multiple
databases.
David|||Thanks David,
So for 1) i'm not sure you've answered the question. I know Workgroup
Edition is 32bit but it can run on Windows 2003 Standard 64bit edition using
WOW. Now because i have a 64bit Opteron will i see SQL run quicker in this
config as opposed to using Windows 2003 Standard 32bit Server? Also are
there any performance penalties using WOW?
For 3) i take it you mean you can only have one prinicipal server per
mirror?
So if i have two SQL server machines running different databases and i need
to use just 1 backup server incase one machine fails what are my best
options?
Log-Shipping? or Replication?
Clustering would be too expensive for us and Mirroring would also be too
expensive if you need one mirror for each SQL server machine.
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
> and lacks the tuning tools of SQL Server.
>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
>> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring functionality
>> : is it possible to mirror multiple sql servers to just one failover
>> mirror or do you need a separate sql box to mirror each one ?
> One mirror per database, although the mirror server can mirror multiple
> databases.
> David
>|||Most of the benefits of 64 bit architectures are a result of the flat memory
space but Workgroup Edition is not going to be able to take advantage of
most of that. running a 32 bit edition in WOW allows 4 GB of user space so
there is some advantage there. Currently, 64 bit processors are generally
slower than the fastest 32 bit processors so in some cases a 32 bit
processor might actually give better performance than a 64 bit processor and
WOW. Of course, with a 64 bit machine you have the advantage of being able
to upgrade to Standard edition and take advantage of the flat memory space
if you run out of horsepower in WOW.
You can have multiple servers mirrored to a single DB Mirroring server
because individual databases are mirrored. You would have to be careful to
ensure that the mirror has enough resources to both handle the load of
mirroring databases from several server and the load of running the
databases when they're failed over.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
> Thanks David,
> So for 1) i'm not sure you've answered the question. I know Workgroup
> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
> in this config as opposed to using Windows 2003 Standard 32bit Server?
> Also are there any performance penalties using WOW?
> For 3) i take it you mean you can only have one prinicipal server per
> mirror?
> So if i have two SQL server machines running different databases and i
> need to use just 1 backup server incase one machine fails what are my best
> options?
> Log-Shipping? or Replication?
> Clustering would be too expensive for us and Mirroring would also be too
> expensive if you need one mirror for each SQL server machine.
> Thanks.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
>> and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
>> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each one
>> ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>|||Ok great.
Is there anything required to activate WOW on windows server or does it
automatically just work when you install the Workgroup Edition?
Also can you just confirm that the following scenario is doable:
Two SQL machines with copy of SQL 2005 Standard Edition
named SQL1 and SQL2 (both with there own separate internal ip address)
SQL1 has two databases called DB1 and DB2
SQL2 has two databases called DB3 and DB4
Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the Mirror
Witness Server (WIT1)
DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
Witness Server
Now if both SQL1 and SQ2 fail at the same time can the Mirror take over all
the connections to these servers?
Thanks.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
> Most of the benefits of 64 bit architectures are a result of the flat
> memory space but Workgroup Edition is not going to be able to take
> advantage of most of that. running a 32 bit edition in WOW allows 4 GB of
> user space so there is some advantage there. Currently, 64 bit processors
> are generally slower than the fastest 32 bit processors so in some cases a
> 32 bit processor might actually give better performance than a 64 bit
> processor and WOW. Of course, with a 64 bit machine you have the
> advantage of being able to upgrade to Standard edition and take advantage
> of the flat memory space if you run out of horsepower in WOW.
> You can have multiple servers mirrored to a single DB Mirroring server
> because individual databases are mirrored. You would have to be careful
> to ensure that the mirror has enough resources to both handle the load of
> mirroring databases from several server and the load of running the
> databases when they're failed over.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "greg" <greg_platt@.hotmail.com> wrote in message
> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>> Thanks David,
>> So for 1) i'm not sure you've answered the question. I know Workgroup
>> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
>> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
>> in this config as opposed to using Windows 2003 Standard 32bit Server?
>> Also are there any performance penalties using WOW?
>> For 3) i take it you mean you can only have one prinicipal server per
>> mirror?
>> So if i have two SQL server machines running different databases and i
>> need to use just 1 backup server incase one machine fails what are my
>> best options?
>> Log-Shipping? or Replication?
>> Clustering would be too expensive for us and Mirroring would also be too
>> expensive if you need one mirror for each SQL server machine.
>> Thanks.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of RAM,
>> and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard
>> Edition vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit
>> Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each
>> one ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>>
>|||WOW is always there. Any 32 bit software you install will run in the WOW.
Theoretically, your failover scenario will work if you keep in mind that the
mirror server will have to handle four databases in this case so unless this
server has significantly more power than the primary servers, performance
will suffer if you fail over all the databases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"greg" <greg_platt@.hotmail.com> wrote in message
news:OmGebPlGGHA.3448@.TK2MSFTNGP10.phx.gbl...
> Ok great.
> Is there anything required to activate WOW on windows server or does it
> automatically just work when you install the Workgroup Edition?
> Also can you just confirm that the following scenario is doable:
> Two SQL machines with copy of SQL 2005 Standard Edition
> named SQL1 and SQL2 (both with there own separate internal ip address)
> SQL1 has two databases called DB1 and DB2
> SQL2 has two databases called DB3 and DB4
> Spare Server (SPARE1) with copy of SQL 2005 Standard Edition for the
> Mirror
> Witness Server (WIT1)
> DB1,DB2,DB3,DB4 are mirrored to the Spare Server (SPARE1) with WIT1 as the
> Witness Server
> Now if both SQL1 and SQ2 fail at the same time can the Mirror take over
> all the connections to these servers?
> Thanks.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23DS1h9jGGHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Most of the benefits of 64 bit architectures are a result of the flat
>> memory space but Workgroup Edition is not going to be able to take
>> advantage of most of that. running a 32 bit edition in WOW allows 4 GB
>> of user space so there is some advantage there. Currently, 64 bit
>> processors are generally slower than the fastest 32 bit processors so in
>> some cases a 32 bit processor might actually give better performance than
>> a 64 bit processor and WOW. Of course, with a 64 bit machine you have
>> the advantage of being able to upgrade to Standard edition and take
>> advantage of the flat memory space if you run out of horsepower in WOW.
>> You can have multiple servers mirrored to a single DB Mirroring server
>> because individual databases are mirrored. You would have to be careful
>> to ensure that the mirror has enough resources to both handle the load of
>> mirroring databases from several server and the load of running the
>> databases when they're failed over.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:uBwm4ejGGHA.3916@.TK2MSFTNGP10.phx.gbl...
>> Thanks David,
>> So for 1) i'm not sure you've answered the question. I know Workgroup
>> Edition is 32bit but it can run on Windows 2003 Standard 64bit edition
>> using WOW. Now because i have a 64bit Opteron will i see SQL run quicker
>> in this config as opposed to using Windows 2003 Standard 32bit Server?
>> Also are there any performance penalties using WOW?
>> For 3) i take it you mean you can only have one prinicipal server per
>> mirror?
>> So if i have two SQL server machines running different databases and i
>> need to use just 1 backup server incase one machine fails what are my
>> best options?
>> Log-Shipping? or Replication?
>> Clustering would be too expensive for us and Mirroring would also be too
>> expensive if you need one mirror for each SQL server machine.
>> Thanks.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%237r4ZseGGHA.1032@.TK2MSFTNGP15.phx.gbl...
>> "greg" <greg_platt@.hotmail.com> wrote in message
>> news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a few questions regarding sql server 2005 .
>> Hardware will be running with a single dual core 64 bit Opteron 175.
>> 1) Are there any performance advantages running sql 2005 Workgroup
>> Edition on Windows 2003 64 bit Edition? If so what are they?
>>
>> Yes. Workgroup Edition only comes in 32bit and can only use 3GB of
>> RAM, and lacks the tuning tools of SQL Server.
>>
>> 2) Are there any benchmarks on how much faster sql 2005 Standard
>> Edition vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit
>> Edition?
>> No. For small workloads there shouldn't be much difference.
>> 3) For sql 2005 Standard Edition and the database mirroring
>> functionality : is it possible to mirror multiple sql servers to just
>> one failover mirror or do you need a separate sql box to mirror each
>> one ?
>>
>> One mirror per database, although the mirror server can mirror multiple
>> databases.
>> David
>>
>>
>|||1. Each principal can have exactly 1 mirror. A witness can service as many
principal/mirror pairs as you want it to. Mirroring occurs at a database
level, so you can have an instance of SQL Server where there are multiple
databases on that instance in the role of a mirror.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"greg" <greg_platt@.hotmail.com> wrote in message
news:%23Y7SECZGGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I have a few questions regarding sql server 2005 .
> Hardware will be running with a single dual core 64 bit Opteron 175.
> 1) Are there any performance advantages running sql 2005 Workgroup Edition
> on Windows 2003 64 bit Edition? If so what are they?
> 2) Are there any benchmarks on how much faster sql 2005 Standard Edition
> vs sql 2005 Workgroup Edition runs on Windows 2003 64 bit Edition?
> 3) For sql 2005 Standard Edition and the database mirroring functionality
> : is it possible to mirror multiple sql servers to just one failover
> mirror or do you need a separate sql box to mirror each one ?
> Thanks
>
Wednesday, March 21, 2012
minimum memory per query: how does it work?
Hi,
How does this "minimum memory per query" option work in SQL Server 2005?
Does it have any influence on performance? When it is put higher than the
standard value, does this mean that more memory will be reserved for each
query, and he will have a hard time to run multiple queries at the same
time?
thanks a lot and kind regards,
PieterYou can use sp_configure to set this a desired value. Check SQL Server Books
Online for the exact syntax.
For light weight databases, it may be a good option to try. However, if you
have a large database with long running transactions on massive datasets or
large number of concurrent connections, using min memory settings is not
recommended. In most cases, it is better to leave it to default since the
query processor usually can figure out the optimal memory allocation for
simple queries.
--
Anith
How does this "minimum memory per query" option work in SQL Server 2005?
Does it have any influence on performance? When it is put higher than the
standard value, does this mean that more memory will be reserved for each
query, and he will have a hard time to run multiple queries at the same
time?
thanks a lot and kind regards,
PieterYou can use sp_configure to set this a desired value. Check SQL Server Books
Online for the exact syntax.
For light weight databases, it may be a good option to try. However, if you
have a large database with long running transactions on massive datasets or
large number of concurrent connections, using min memory settings is not
recommended. In most cases, it is better to leave it to default since the
query processor usually can figure out the optimal memory allocation for
simple queries.
--
Anith
Monday, March 19, 2012
Minimal configuration, suggestions wanted.
Minimal configuration.
(Performance is not an issue).
Dataloss should not happen. (But we do not account
for real dissasters, for example a big fire destroying
the complete production center, resulting
in the dataloss of 24 hours would be acceptable.)
The shop runs 7 x 24 hours.
Most is done during office hours.
Some is done during all day.
At night the system must be available but it is
used very limited.
Once a day a tape goes from the production machine to an
offsite safe.
The total amount of data has a disk footprint off
between 1 and 10 Gigabytes.
Offcourse there is a production machine and
a standby machine probably in another location.
(The second machine will be used for testing and
practicing procedures.)
Raid is required, raid protects agains single disks failures,
but what are the options for more protection ?
What configuration is considered minimal in these circumstances ?
Esspecially what to do with the disks ?
(OS / Logging / Data / Backupdata / Transactionlog backup) ?
What can be combined and what can not be combined ?
And how often to do the transactionlog backup ?
(Side question what "route" should the transactionlog(-backup)
take).
Keep in mind : We are looking for a minimal configuration.
Later on we will also looking for a 'larger' configuration with
more performance, a larger database etc. (But still no super
configuration. Something like 100-200 Gigabyte and more load).
Thanks for your thoughts,
ben brugman.Keep in mind that a server with a 200GB db and moderate load may not be the
same for a 10GB db with light load and this is addressing the latter as you
asked. A RAID 0+1 will give the most fault tolerate Raid with good
performance as well. If the load is light enough (not a lot of
transactions) then you can probably get away with a good size (meaning # of
disks not size of disks) RAID 0+1 or even 1+0 that has everything on it.
But it is usually best to place the logs on their own RAID 1 and separate
from the data for performance reasons. With a minimal configuration a good
raid is essential along with proper backups. A full backup once a night is
usually OK for most applications. The frequency of the log backups depend
on how much data you are willing to loose. If you can afford to loose up to
15 minutes worth of transactions then use a 15 minute interval etc. Just
ensure the backups are not done to the same drive the logs or data reside
on.
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:u1zBcW7GEHA.688@.tk2msftngp13.phx.gbl...
> Minimal configuration.
> (Performance is not an issue).
> Dataloss should not happen. (But we do not account
> for real dissasters, for example a big fire destroying
> the complete production center, resulting
> in the dataloss of 24 hours would be acceptable.)
> The shop runs 7 x 24 hours.
> Most is done during office hours.
> Some is done during all day.
> At night the system must be available but it is
> used very limited.
> Once a day a tape goes from the production machine to an
> offsite safe.
> The total amount of data has a disk footprint off
> between 1 and 10 Gigabytes.
> Offcourse there is a production machine and
> a standby machine probably in another location.
> (The second machine will be used for testing and
> practicing procedures.)
> Raid is required, raid protects agains single disks failures,
> but what are the options for more protection ?
> What configuration is considered minimal in these circumstances ?
> Esspecially what to do with the disks ?
> (OS / Logging / Data / Backupdata / Transactionlog backup) ?
> What can be combined and what can not be combined ?
> And how often to do the transactionlog backup ?
> (Side question what "route" should the transactionlog(-backup)
> take).
>
> Keep in mind : We are looking for a minimal configuration.
> Later on we will also looking for a 'larger' configuration with
> more performance, a larger database etc. (But still no super
> configuration. Something like 100-200 Gigabyte and more load).
> Thanks for your thoughts,
> ben brugman.
>|||Going with your suggestions,
suppose you have split the total RAID device in 3 groups
of disks (fysical separate), how would you place
1 OS
2 Logging
3 Data
4 Backupdata
5 Transactionlog backup
Have I missed anything ?
Or are 2 groups enough ?
Or do I need more groups ?
> on how much data you are willing to loose. If you can afford to loose up
to
> 15 minutes worth of transactions then use a 15 minute interval etc. Just
If I go from estimates of MTBF of disks the chance of
losing a mirrored disk is extremely small. So the chance
of loosing up to 15 minutes of data is extremely small.
Loosing the complete system because of a major
dissaster (fire for example) looks more likely and then
you loose everything since the last time you brought
the backup to outside of the 'box' or the computerroom.
Because for a minimal system bringing the data out of the
room more than once or twice a day would probably be a
problem.
(Calculation for 2 disks failing, assuming a MTBF of 300000 hours,
that a failed disk is spotted with in 24 hours. I get a failure of
two disks every once in 1/(24/300000)^2 days = 156250000 days)
Two of my problems :
1. Backup of the transaction log is not a 'backup' but a 'move' of the
'content',
so how do I protect the backup of the transaction log, because at that
moment
it is the only 'copy' of that data in existence. (Still mirrored I would
suggest).
2. If part of the chain of transactionlogs (active of backupped) is lost the
datafiles are worthless, so I do not understand that the datafiles
should be on a separate disk.
(Performance not being a consideration).
Thanks for your thoughts.
ben brugman
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uRptBADHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> Keep in mind that a server with a 200GB db and moderate load may not be
the
> same for a 10GB db with light load and this is addressing the latter as
you
> asked. A RAID 0+1 will give the most fault tolerate Raid with good
> performance as well. If the load is light enough (not a lot of
> transactions) then you can probably get away with a good size (meaning #
of
> disks not size of disks) RAID 0+1 or even 1+0 that has everything on it.
> But it is usually best to place the logs on their own RAID 1 and separate
> from the data for performance reasons. With a minimal configuration a
good
> raid is essential along with proper backups. A full backup once a night
is
> usually OK for most applications. The frequency of the log backups depend
> on how much data you are willing to loose. If you can afford to loose up
to
> 15 minutes worth of transactions then use a 15 minute interval etc. Just
> ensure the backups are not done to the same drive the logs or data reside
> on.
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u1zBcW7GEHA.688@.tk2msftngp13.phx.gbl...
>|||It depends on how you will use the database. If you have a lot of
operations that use tempdb heavily you may wish to place tempdb on it's own
raid as well. Logs should be on their own array unless it is mostly read
only. A typical config for a moderate system is usually something like
this:
RAID 1 OS / SQL Binaries
RAID 1 SQL Transaction Logs
RAID 10 DATA, TempDB
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:c50moh$gtq$1@.reader11.wxs.nl...
> Going with your suggestions,
> suppose you have split the total RAID device in 3 groups
> of disks (fysical separate), how would you place
> 1 OS
> 2 Logging
> 3 Data
> 4 Backupdata
> 5 Transactionlog backup
> Have I missed anything ?
> Or are 2 groups enough ?
> Or do I need more groups ?
>
up
> to
> If I go from estimates of MTBF of disks the chance of
> losing a mirrored disk is extremely small. So the chance
> of loosing up to 15 minutes of data is extremely small.
> Loosing the complete system because of a major
> dissaster (fire for example) looks more likely and then
> you loose everything since the last time you brought
> the backup to outside of the 'box' or the computerroom.
> Because for a minimal system bringing the data out of the
> room more than once or twice a day would probably be a
> problem.
> (Calculation for 2 disks failing, assuming a MTBF of 300000 hours,
> that a failed disk is spotted with in 24 hours. I get a failure of
> two disks every once in 1/(24/300000)^2 days = 156250000 days)
> Two of my problems :
> 1. Backup of the transaction log is not a 'backup' but a 'move' of the
> 'content',
> so how do I protect the backup of the transaction log, because at that
> moment
> it is the only 'copy' of that data in existence. (Still mirrored I would
> suggest).
> 2. If part of the chain of transactionlogs (active of backupped) is lost
the
> datafiles are worthless, so I do not understand that the datafiles
> should be on a separate disk.
> (Performance not being a consideration).
> Thanks for your thoughts.
> ben brugman
>
>
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:uRptBADHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> the
> you
> of
separate
> good
> is
depend
up
> to
reside
>|||>
> RAID 1 OS / SQL Binaries
> RAID 1 SQL Transaction Logs
> RAID 10 DATA, TempDB
>
What about :
I still have difficulty in understanding why different groups
offer more 'protection' than less different groups.
In your schema at least 8 disks are needed to realise
the solution. (1 each for the RAID 1 solution and at least
four for the RAID 10 solution. This is often the maximum
number of disks a 'simple' RAID box supports).
IF (this is supposed by me, so you can point out the
error or mistake in my reasoning.)
IF I put everything OS/Log/Data on a single group of
RAID disks the worst that can happen is that two mirrored
disks fail, resulting in the dataloss since the last backup and
move to outside the system.
But with the proposed system of having OS/Log/Data each on
their own group, the risk of losing the Log is the same because
losing the mirrored disk (with only the logs) gives us the
same amount of dataloss as with everything on the same group.
I very strongly feel that I AM MISSING SOME UNDERSTANDING
of this situation.
(Offcourse if the volumes of data and log become larger and
performance is an issue, I would go with more groups and understand
the reasons for this.).
My question is : Why is it wrong to put OS/Log/Data on a single
group of disks ?
(What is the risc of this ? Or why are there more riscs for this
than 3 groups of disks ?)
Everybody (books, discussions, usegroups) that this should
not be done, but except for performance the reason(s) is (are) not
explained.
Thanks again for your time,
and sorry to go on on this subject, but I really would like to
understand it.
ben brugman
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c50moh$gtq$1@.reader11.wxs.nl...
> up
Just
> the
be
as
#
it.
> separate
night
> depend
> up
Just
> reside
>|||The main reason to separate them is for performance and scalability. While
there is some added protection for data loss with separate arrays over just
one it is secondary to performance and maintenance. If all your worried
about is a minimal configuration then use a single RAID 10 and be done with
it. This will suite many smaller applications just fine. If performance
becomes an issue and it is attributed to disk I/O you can look into adding
another RAID 1 for the logs etc.
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eFLE5oUHEHA.2836@.TK2MSFTNGP11.phx.gbl...
> What about :
> I still have difficulty in understanding why different groups
> offer more 'protection' than less different groups.
> In your schema at least 8 disks are needed to realise
> the solution. (1 each for the RAID 1 solution and at least
> four for the RAID 10 solution. This is often the maximum
> number of disks a 'simple' RAID box supports).
> IF (this is supposed by me, so you can point out the
> error or mistake in my reasoning.)
> IF I put everything OS/Log/Data on a single group of
> RAID disks the worst that can happen is that two mirrored
> disks fail, resulting in the dataloss since the last backup and
> move to outside the system.
> But with the proposed system of having OS/Log/Data each on
> their own group, the risk of losing the Log is the same because
> losing the mirrored disk (with only the logs) gives us the
> same amount of dataloss as with everything on the same group.
> I very strongly feel that I AM MISSING SOME UNDERSTANDING
> of this situation.
> (Offcourse if the volumes of data and log become larger and
> performance is an issue, I would go with more groups and understand
> the reasons for this.).
> My question is : Why is it wrong to put OS/Log/Data on a single
> group of disks ?
> (What is the risc of this ? Or why are there more riscs for this
> than 3 groups of disks ?)
> Everybody (books, discussions, usegroups) that this should
> not be done, but except for performance the reason(s) is (are) not
> explained.
> Thanks again for your time,
> and sorry to go on on this subject, but I really would like to
> understand it.
> ben brugman
>
loose
> Just
would
lost
> be
> as
(meaning
> #
> it.
a
> night
loose
> Just
>|||"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ODHMefdHEHA.2948@.TK2MSFTNGP11.phx.gbl...
> The main reason to separate them is for performance and scalability.
While
> there is some added protection for data loss with separate arrays over
just
> one it is secondary to performance and maintenance. If all your worried
> about is a minimal configuration then use a single RAID 10 and be done
with
> it. This will suite many smaller applications just fine. If performance
> becomes an issue and it is attributed to disk I/O you can look into adding
> another RAID 1 for the logs etc.
>
Hello Andrew,
Thanks for your anwser,
We will probably go for a RAID 10 solution, were all
SQL-server data and log is stored on one diskgroup.
But we will probably have an offsite copy of this RAID 10
solution, which will be a hardware mirror of the first
RAID 10 box.
For the larger systems a SAN will be used, but this will
be to expensive for the smaller locations.
Thanks for your time.
ben brugman
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eFLE5oUHEHA.2836@.TK2MSFTNGP11.phx.gbl...
> loose
etc.
the
that
> would
> lost
not
latter
good
> (meaning
on
configuration
> a
backups
> loose
etc.
?
>
(Performance is not an issue).
Dataloss should not happen. (But we do not account
for real dissasters, for example a big fire destroying
the complete production center, resulting
in the dataloss of 24 hours would be acceptable.)
The shop runs 7 x 24 hours.
Most is done during office hours.
Some is done during all day.
At night the system must be available but it is
used very limited.
Once a day a tape goes from the production machine to an
offsite safe.
The total amount of data has a disk footprint off
between 1 and 10 Gigabytes.
Offcourse there is a production machine and
a standby machine probably in another location.
(The second machine will be used for testing and
practicing procedures.)
Raid is required, raid protects agains single disks failures,
but what are the options for more protection ?
What configuration is considered minimal in these circumstances ?
Esspecially what to do with the disks ?
(OS / Logging / Data / Backupdata / Transactionlog backup) ?
What can be combined and what can not be combined ?
And how often to do the transactionlog backup ?
(Side question what "route" should the transactionlog(-backup)
take).
Keep in mind : We are looking for a minimal configuration.
Later on we will also looking for a 'larger' configuration with
more performance, a larger database etc. (But still no super
configuration. Something like 100-200 Gigabyte and more load).
Thanks for your thoughts,
ben brugman.Keep in mind that a server with a 200GB db and moderate load may not be the
same for a 10GB db with light load and this is addressing the latter as you
asked. A RAID 0+1 will give the most fault tolerate Raid with good
performance as well. If the load is light enough (not a lot of
transactions) then you can probably get away with a good size (meaning # of
disks not size of disks) RAID 0+1 or even 1+0 that has everything on it.
But it is usually best to place the logs on their own RAID 1 and separate
from the data for performance reasons. With a minimal configuration a good
raid is essential along with proper backups. A full backup once a night is
usually OK for most applications. The frequency of the log backups depend
on how much data you are willing to loose. If you can afford to loose up to
15 minutes worth of transactions then use a 15 minute interval etc. Just
ensure the backups are not done to the same drive the logs or data reside
on.
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:u1zBcW7GEHA.688@.tk2msftngp13.phx.gbl...
> Minimal configuration.
> (Performance is not an issue).
> Dataloss should not happen. (But we do not account
> for real dissasters, for example a big fire destroying
> the complete production center, resulting
> in the dataloss of 24 hours would be acceptable.)
> The shop runs 7 x 24 hours.
> Most is done during office hours.
> Some is done during all day.
> At night the system must be available but it is
> used very limited.
> Once a day a tape goes from the production machine to an
> offsite safe.
> The total amount of data has a disk footprint off
> between 1 and 10 Gigabytes.
> Offcourse there is a production machine and
> a standby machine probably in another location.
> (The second machine will be used for testing and
> practicing procedures.)
> Raid is required, raid protects agains single disks failures,
> but what are the options for more protection ?
> What configuration is considered minimal in these circumstances ?
> Esspecially what to do with the disks ?
> (OS / Logging / Data / Backupdata / Transactionlog backup) ?
> What can be combined and what can not be combined ?
> And how often to do the transactionlog backup ?
> (Side question what "route" should the transactionlog(-backup)
> take).
>
> Keep in mind : We are looking for a minimal configuration.
> Later on we will also looking for a 'larger' configuration with
> more performance, a larger database etc. (But still no super
> configuration. Something like 100-200 Gigabyte and more load).
> Thanks for your thoughts,
> ben brugman.
>|||Going with your suggestions,
suppose you have split the total RAID device in 3 groups
of disks (fysical separate), how would you place
1 OS
2 Logging
3 Data
4 Backupdata
5 Transactionlog backup
Have I missed anything ?
Or are 2 groups enough ?
Or do I need more groups ?
> on how much data you are willing to loose. If you can afford to loose up
to
> 15 minutes worth of transactions then use a 15 minute interval etc. Just
If I go from estimates of MTBF of disks the chance of
losing a mirrored disk is extremely small. So the chance
of loosing up to 15 minutes of data is extremely small.
Loosing the complete system because of a major
dissaster (fire for example) looks more likely and then
you loose everything since the last time you brought
the backup to outside of the 'box' or the computerroom.
Because for a minimal system bringing the data out of the
room more than once or twice a day would probably be a
problem.
(Calculation for 2 disks failing, assuming a MTBF of 300000 hours,
that a failed disk is spotted with in 24 hours. I get a failure of
two disks every once in 1/(24/300000)^2 days = 156250000 days)
Two of my problems :
1. Backup of the transaction log is not a 'backup' but a 'move' of the
'content',
so how do I protect the backup of the transaction log, because at that
moment
it is the only 'copy' of that data in existence. (Still mirrored I would
suggest).
2. If part of the chain of transactionlogs (active of backupped) is lost the
datafiles are worthless, so I do not understand that the datafiles
should be on a separate disk.
(Performance not being a consideration).
Thanks for your thoughts.
ben brugman
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uRptBADHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> Keep in mind that a server with a 200GB db and moderate load may not be
the
> same for a 10GB db with light load and this is addressing the latter as
you
> asked. A RAID 0+1 will give the most fault tolerate Raid with good
> performance as well. If the load is light enough (not a lot of
> transactions) then you can probably get away with a good size (meaning #
of
> disks not size of disks) RAID 0+1 or even 1+0 that has everything on it.
> But it is usually best to place the logs on their own RAID 1 and separate
> from the data for performance reasons. With a minimal configuration a
good
> raid is essential along with proper backups. A full backup once a night
is
> usually OK for most applications. The frequency of the log backups depend
> on how much data you are willing to loose. If you can afford to loose up
to
> 15 minutes worth of transactions then use a 15 minute interval etc. Just
> ensure the backups are not done to the same drive the logs or data reside
> on.
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u1zBcW7GEHA.688@.tk2msftngp13.phx.gbl...
>|||It depends on how you will use the database. If you have a lot of
operations that use tempdb heavily you may wish to place tempdb on it's own
raid as well. Logs should be on their own array unless it is mostly read
only. A typical config for a moderate system is usually something like
this:
RAID 1 OS / SQL Binaries
RAID 1 SQL Transaction Logs
RAID 10 DATA, TempDB
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:c50moh$gtq$1@.reader11.wxs.nl...
> Going with your suggestions,
> suppose you have split the total RAID device in 3 groups
> of disks (fysical separate), how would you place
> 1 OS
> 2 Logging
> 3 Data
> 4 Backupdata
> 5 Transactionlog backup
> Have I missed anything ?
> Or are 2 groups enough ?
> Or do I need more groups ?
>
up
> to
> If I go from estimates of MTBF of disks the chance of
> losing a mirrored disk is extremely small. So the chance
> of loosing up to 15 minutes of data is extremely small.
> Loosing the complete system because of a major
> dissaster (fire for example) looks more likely and then
> you loose everything since the last time you brought
> the backup to outside of the 'box' or the computerroom.
> Because for a minimal system bringing the data out of the
> room more than once or twice a day would probably be a
> problem.
> (Calculation for 2 disks failing, assuming a MTBF of 300000 hours,
> that a failed disk is spotted with in 24 hours. I get a failure of
> two disks every once in 1/(24/300000)^2 days = 156250000 days)
> Two of my problems :
> 1. Backup of the transaction log is not a 'backup' but a 'move' of the
> 'content',
> so how do I protect the backup of the transaction log, because at that
> moment
> it is the only 'copy' of that data in existence. (Still mirrored I would
> suggest).
> 2. If part of the chain of transactionlogs (active of backupped) is lost
the
> datafiles are worthless, so I do not understand that the datafiles
> should be on a separate disk.
> (Performance not being a consideration).
> Thanks for your thoughts.
> ben brugman
>
>
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:uRptBADHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> the
> you
> of
separate
> good
> is
depend
up
> to
reside
>|||>
> RAID 1 OS / SQL Binaries
> RAID 1 SQL Transaction Logs
> RAID 10 DATA, TempDB
>
What about :
I still have difficulty in understanding why different groups
offer more 'protection' than less different groups.
In your schema at least 8 disks are needed to realise
the solution. (1 each for the RAID 1 solution and at least
four for the RAID 10 solution. This is often the maximum
number of disks a 'simple' RAID box supports).
IF (this is supposed by me, so you can point out the
error or mistake in my reasoning.)
IF I put everything OS/Log/Data on a single group of
RAID disks the worst that can happen is that two mirrored
disks fail, resulting in the dataloss since the last backup and
move to outside the system.
But with the proposed system of having OS/Log/Data each on
their own group, the risk of losing the Log is the same because
losing the mirrored disk (with only the logs) gives us the
same amount of dataloss as with everything on the same group.
I very strongly feel that I AM MISSING SOME UNDERSTANDING
of this situation.
(Offcourse if the volumes of data and log become larger and
performance is an issue, I would go with more groups and understand
the reasons for this.).
My question is : Why is it wrong to put OS/Log/Data on a single
group of disks ?
(What is the risc of this ? Or why are there more riscs for this
than 3 groups of disks ?)
Everybody (books, discussions, usegroups) that this should
not be done, but except for performance the reason(s) is (are) not
explained.
Thanks again for your time,
and sorry to go on on this subject, but I really would like to
understand it.
ben brugman
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c50moh$gtq$1@.reader11.wxs.nl...
> up
Just
> the
be
as
#
it.
> separate
night
> depend
> up
Just
> reside
>|||The main reason to separate them is for performance and scalability. While
there is some added protection for data loss with separate arrays over just
one it is secondary to performance and maintenance. If all your worried
about is a minimal configuration then use a single RAID 10 and be done with
it. This will suite many smaller applications just fine. If performance
becomes an issue and it is attributed to disk I/O you can look into adding
another RAID 1 for the logs etc.
Andrew J. Kelly SQL MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eFLE5oUHEHA.2836@.TK2MSFTNGP11.phx.gbl...
> What about :
> I still have difficulty in understanding why different groups
> offer more 'protection' than less different groups.
> In your schema at least 8 disks are needed to realise
> the solution. (1 each for the RAID 1 solution and at least
> four for the RAID 10 solution. This is often the maximum
> number of disks a 'simple' RAID box supports).
> IF (this is supposed by me, so you can point out the
> error or mistake in my reasoning.)
> IF I put everything OS/Log/Data on a single group of
> RAID disks the worst that can happen is that two mirrored
> disks fail, resulting in the dataloss since the last backup and
> move to outside the system.
> But with the proposed system of having OS/Log/Data each on
> their own group, the risk of losing the Log is the same because
> losing the mirrored disk (with only the logs) gives us the
> same amount of dataloss as with everything on the same group.
> I very strongly feel that I AM MISSING SOME UNDERSTANDING
> of this situation.
> (Offcourse if the volumes of data and log become larger and
> performance is an issue, I would go with more groups and understand
> the reasons for this.).
> My question is : Why is it wrong to put OS/Log/Data on a single
> group of disks ?
> (What is the risc of this ? Or why are there more riscs for this
> than 3 groups of disks ?)
> Everybody (books, discussions, usegroups) that this should
> not be done, but except for performance the reason(s) is (are) not
> explained.
> Thanks again for your time,
> and sorry to go on on this subject, but I really would like to
> understand it.
> ben brugman
>
loose
> Just
would
lost
> be
> as
(meaning
> #
> it.
a
> night
loose
> Just
>|||"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ODHMefdHEHA.2948@.TK2MSFTNGP11.phx.gbl...
> The main reason to separate them is for performance and scalability.
While
> there is some added protection for data loss with separate arrays over
just
> one it is secondary to performance and maintenance. If all your worried
> about is a minimal configuration then use a single RAID 10 and be done
with
> it. This will suite many smaller applications just fine. If performance
> becomes an issue and it is attributed to disk I/O you can look into adding
> another RAID 1 for the logs etc.
>
Hello Andrew,
Thanks for your anwser,
We will probably go for a RAID 10 solution, were all
SQL-server data and log is stored on one diskgroup.
But we will probably have an offsite copy of this RAID 10
solution, which will be a hardware mirror of the first
RAID 10 box.
For the larger systems a SAN will be used, but this will
be to expensive for the smaller locations.
Thanks for your time.
ben brugman
> --
> Andrew J. Kelly SQL MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eFLE5oUHEHA.2836@.TK2MSFTNGP11.phx.gbl...
> loose
etc.
the
that
> would
> lost
not
latter
good
> (meaning
on
configuration
> a
backups
> loose
etc.
?
>
Wednesday, March 7, 2012
Migration from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Serv
We are migration few database from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)
Problem :
- The performance difference observer was not much (Just 15-18%)
- The CPU Utilization goes to 100% immediately, when the software login screen appears and it sustains in between 90% to 100%
- Multi-User Access (3 or more concurrent users), the sql performance is tremendously slow.
Steps followed for Migration:
- We have used the backup and restore method to migrate the data from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server).
- We ran the DBCC DBREINDEX for each affected tables
- We executed the sp_updatestats procedure for all the tables
- Upgraded the DB compatibility version to 90
- Installed the SQL 2005 Enterprise Server (64 bit-Itanium Server) Service pack 2.
One thing that we observed, was the memory usage, on SQL 2000 the sqlservr.exe uses around 1.5 GB where as on the SQL 2005 its just 120 MB.
A normal select query on 2000 Server took 11-12 sec, having 10 lac records, and the same query on 2005 took 1 min (3-5 sec CPU process taken by sqlservr and rest by the Sever Management Studio.
The only differance noted was that on 2000 the entire data was displayed at one go where as in 2005 the data was displayed at the 1st sec, and was filling the rest in the buffer, and to finally display entire data it took 1 min.
Also
when we run this query
select * from sys.dm_os_sys_info
one column os_priority_class shows a value = 32 ? what does this means.
In the MSSQLSERVER SERVICES, properties, there is a parameter displaying value
Advanced > Running Under 64 bit OS = No ? what does this means.
Could anyone pls suggest as to how to increase the sqlservr.exe memory area, and also how the enhance the performance.
Hi Ashok,
Running Under 64 bit OS =No means that Operating System is not 64 bit version, you are running 64 bit Version of SQL Server on Operating System which is 32 bit.
Regarding SQL Server Memory it is Dynamically Allocated and De Allocated unless you have specified using sp_configure or via SSMS, another thing you need to refer is have you or some one change the values for network packet size / Min memory / maximum memory value
have you set any option for max degree of parallelism Or max worker threads
If you do have more then 2 GB memory you need to use AWE OR PAE switch in order to use memory more then 2 GB , also refer Estimated and Actual Exucution Plan in both the server to get idea for taking too much time in new server.
Refer http://www.sql-server-performance.com/statistics_io_time.asp
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17732
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18192
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17107
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16988
HTH
Hemantgiri S. Goswami
Migration from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium
We are migration few database from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)
Problem :
- The performance difference observer was not much (Just 15-18%)
- The CPU Utilization goes to 100% immediately, when the software login screen appears and it sustains in between 90% to 100%
- Multi-User Access (3 or more concurrent users), the sql performance is tremendously slow.
Steps followed for Migration:
- We have used the backup and restore method to migrate the data from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server).
- We ran the DBCC DBREINDEX for each affected tables
- We executed the sp_updatestats procedure for all the tables
- Upgraded the DB compatibility version to 90
- Installed the SQL 2005 Enterprise Server (64 bit-Itanium Server) Service pack 2.
One thing that we observed, was the memory usage, on SQL 2000 the sqlservr.exe uses around 1.5 GB where as on the SQL 2005 its just 120 MB.
A normal select query on 2000 Server took 11-12 sec, having 10 lac records, and the same query on 2005 took 1 min (3-5 sec CPU process taken by sqlservr and rest by the Sever Management Studio.
The only differance noted was that on 2000 the entire data was displayed at one go where as in 2005 the data was displayed at the 1st sec, and was filling the rest in the buffer, and to finally display entire data it took 1 min.
Also
when we run this query
select * from sys.dm_os_sys_info
one column os_priority_class shows a value = 32 ? what does this means.
In the MSSQLSERVER SERVICES, properties, there is a parameter displaying value
Advanced > Running Under 64 bit OS = No ? what does this means.
Could anyone pls suggest as to how to increase the sqlservr.exe memory area, and also how the enhance the performance.
Hi Ashok,
Running Under 64 bit OS =No means that Operating System is not 64 bit version, you are running 64 bit Version of SQL Server on Operating System which is 32 bit.
Regarding SQL Server Memory it is Dynamically Allocated and De Allocated unless you have specified using sp_configure or via SSMS, another thing you need to refer is have you or some one change the values for network packet size / Min memory / maximum memory value
have you set any option for max degree of parallelism Or max worker threads
If you do have more then 2 GB memory you need to use AWE OR PAE switch in order to use memory more then 2 GB , also refer Estimated and Actual Exucution Plan in both the server to get idea for taking too much time in new server.
Refer http://www.sql-server-performance.com/statistics_io_time.asp
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17732
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18192
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17107
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16988
HTH
Hemantgiri S. Goswami
Subscribe to:
Posts (Atom)