Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

sql

Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

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
>sql

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
>

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
>

Minimum/Maximum of two values

Hallo Cracks,

what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.

What I try to do:

I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):

Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)

like this sqlstatement:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)

instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.

thanks in advance and kind regards :-)
Cappuhave a look at MAX, MIN, and DATEDIFF functions in SQL Server Books Online|||thanks, but Max and Min exists only as aggregate functions.

I have a solution now with CASE, it works but it looks terrible ;-)

here is it:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) AND
dbo.Phases.phasenabschlussist >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) THEN DATEDIFF(day,
CASE WHEN dbo.Phases.phasenstart >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN dbo.Phases.phasenstart ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) END,
CASE WHEN dbo.Phases.phasenabschlussist <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime)
THEN dbo.Phases.phasenabschlussist ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) END)
+ 1 ELSE 0 END AS TageISTJahr0
FROM dbo.Phases´

it works, but do i understand in two years what i did there? ;-)

min with a bit

Hi,

I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.

Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.

Is there an alternative to my query?

Many thanks,
Chrismin(cast(deleted as int))

"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...

> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?

> min(cast(deleted as int))

Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...

Cheers,
Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
> Cheers,
> Chris

Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).

Simon|||How a bit could be (-1) ?

"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buocfi$ub9$1@.ucsnew1.ncl.ac.uk...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...

How about .... min(cast(bit as varchar(1))) ?

Pete Brown
Falls Creek
Oz|||When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).

HTH,
Gert-Jan

Not Me wrote:
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1) in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Igor Raytsin" <n&i@.cyberus.ca> wrote in message
news:400fee3f_1@.news.cybersurf.net...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > I'm trying to grab records with a priority over those marked as yes (-1)
> in
> > a certain field.
> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?
> How a bit could be (-1) ?

Ask Bill :o)

Chris|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0401220728.58b967ae@.posting.google.c om...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> > "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> > news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > > operator doesn't work on bits.
> > > > > > Is there an alternative to my query?
> > > min(cast(deleted as int))
> > Thanks for that, I do though get an error when trying it, I guess it's
> > because I'm using an mdb file and linked tables to the sql server... any
> > other ideas? Could create a quick function I guess...
>
> Your question isn't really clear - a bit column can only hold 0,1 or
> NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
> an MSSQL query, then please consider posting the CREATE TABLE
> statement for your table, as well as the exact query that you're
> using, and the output you expect (sample data would also be useful).

Thanks for your help, yes the -1 just seems to be how access likes to
display the info.

The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.

So, a table could show

id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games

and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)

So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).

So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..

Hope you understand the problem!!
Any help is greatly appreciated.

Cheers,
Chris|||Not Me (Not.Me@.faker.fake.fa.ke) writes:
> So, a table could show
> id current job
> #1 yes databases
> #1 no graphics
> #2 no statistics
> #2 no games
> and I would want to return one record for each id#, with a preference of a
> current job (if no current job, any non-current job will do)
> So far I've only managed to do a "select all current jobs union select
> all non-current jobs that don't appear in the current jobs list" The
> problem here is that it becomes very very slow when performing the "jobs
> that don't appear in the current jobs list" (done by where x not in
> (select x from y)).

Here is one way that you may want to try:

DECLARE @.temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)

INSERT @.temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC

SELECT t.id, c.current, t.job
FROM @.temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @.temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id

By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.

I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

migration from 32 bit to 64 bit

Hi, I want to upgrade a server from a 32 bit system to a 64 bit system, will
i be able to use my same license with the new server or should i need to buy
a new license for the 64 bit server?
Thanks in advance for the help.
Regards,> Hi, I want to upgrade a server from a 32 bit system to a 64 bit system,
> will
> i be able to use my same license with the new server or should i need to
> buy
> a new license for the 64 bit server?
<Excerpt href="http://links.10026.com/?link=">http://www.microsoft.com/sql/howtobuy/default.mspx">
While you should choose the platform that matches your current hardware (in
order to obtain the appropriate media), SQL Server licenses are not platform
specific. For example, if you currently have a 32-bit server and plan to
upgrade the hardware to 64 bit in the future, you can purchase a 32-bit SKU
today, and later switch to 64 bit without having to
purchase an additional license.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:E1927DBA-0E12-47D1-AC7C-2A132DFFC626@.microsoft.com...
> Hi, I want to upgrade a server from a 32 bit system to a 64 bit system,
> will
> i be able to use my same license with the new server or should i need to
> buy
> a new license for the 64 bit server?
> Thanks in advance for the help.
> Regards,
>

Migrating to 64 bit.

I just finished installing MSSQL 2000 to an Itanium cluster and all went
well, but when I tried to use DTS to transfer the users, it says you can't
use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
transfer user accounts since I have several and don't know all the
passwords. I can move the databases, jobs, etc...
Nevermind, I just found this...
http://support.microsoft.com/kb/246133/
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:OhRpg3Q3GHA.324@.TK2MSFTNGP05.phx.gbl...
>I just finished installing MSSQL 2000 to an Itanium cluster and all went
>well, but when I tried to use DTS to transfer the users, it says you can't
>use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
>transfer user accounts since I have several and don't know all the
>passwords. I can move the databases, jobs, etc...
>

Migrating to 64 bit.

I just finished installing MSSQL 2000 to an Itanium cluster and all went
well, but when I tried to use DTS to transfer the users, it says you can't
use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
transfer user accounts since I have several and don't know all the
passwords. I can move the databases, jobs, etc...Nevermind, I just found this...
http://support.microsoft.com/kb/246133/
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:OhRpg3Q3GHA.324@.TK2MSFTNGP05.phx.gbl...
>I just finished installing MSSQL 2000 to an Itanium cluster and all went
>well, but when I tried to use DTS to transfer the users, it says you can't
>use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
>transfer user accounts since I have several and don't know all the
>passwords. I can move the databases, jobs, etc...
>

Migrating to 64 bit.

I just finished installing MSSQL 2000 to an Itanium cluster and all went
well, but when I tried to use DTS to transfer the users, it says you can't
use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
transfer user accounts since I have several and don't know all the
passwords. I can move the databases, jobs, etc...Nevermind, I just found this...
http://support.microsoft.com/kb/246133/
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:OhRpg3Q3GHA.324@.TK2MSFTNGP05.phx.gbl...
>I just finished installing MSSQL 2000 to an Itanium cluster and all went
>well, but when I tried to use DTS to transfer the users, it says you can't
>use 32-bit DTS to transfer to 64-bit. Does anyone have a good trick to
>transfer user accounts since I have several and don't know all the
>passwords. I can move the databases, jobs, etc...
>