Migrating to SQL 2005: principal

Showing posts with label principal. Show all posts

Friday, March 30, 2012

Mirroring Failover/Failback

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.
We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).
Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?
Per SchjetneThe roles for mirroring are soft roles. So, you cannot define a preferred node (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into production now after applying
>SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates (the witness is on a
> webserver in a different domain, so we can't use integrated security).
> Automatic failover works fine if we take down the principal. But when the principal is up and
> running again, no automatic failback occur. If this by design, or is there something I've missed
> out in the configuration?
> Per Schjetne
>|||"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
Sorry, there isn't an option for automatic failback. You will have to do
that manually.
--
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.
--
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
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
> Per Schjetne
>|||I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
> good time to fail back. It's probably much better if you pick a time when
> the change won't affect too many users.
> --
> 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
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||There's an event that you can monitor to be notified when the state changes.
The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.
--
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
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
>is it just monitoring which server is replying, being able to do a
>failover?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> 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
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>|||That's not exactly correct.
The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.
--
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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDV9lIeaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
> ask for it (ALTER DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses
Per :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bsLDGkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> There's an event that you can monitor to be notified when the state
> changes.
> The sys.dm_database_mirroring view can be used to determine the mirroring
> state of all the databases of an instance. The
> sys.dm_database_mirroring_witnesses can be used from the witness to see
> which server is the primary.
> --
> 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
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>>I agree on that. Is there a way to query any system tables telling me
>>which database is acting as principal vs mirror? Is the witness aware of
>>this, or is it just monitoring which server is replying, being able to do
>>a failover?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> 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
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>>
>

Mirroring Failover leaves 2 databases as Principal

We have seven databases mirrrored on our production server. Two of
these failed over, one correctly. The other database is marked as
"Principal/Disconnected" on BOTH the primary and secondary servers. I
can browse the tables in each database. Obviously this is not meant to
happen - one of the databases should be in Restoring mode.Transactions
are only going into the one database - on the Primary server. Has
anyone come across this before? Any ideas on how to proceed?
If I attempt to pause mirroring on the primary server it fails with
the message "The database mirror is busy, re-issue the command later".
I can see this getting really bad if I am unable to (at worst) remove
mirroring from either database due to them thinking that they are the
principal and cannot connect to the other mirrored database.
(both servers on SP2.)On Sep 19, 7:03 pm, Calculated <sarahjco...@.gmail.com> wrote:
> We have seven databases mirrrored on our production server. Two of
> these failed over, one correctly. The other database is marked as
> "Principal/Disconnected" on BOTH the primary and secondary servers. I
> can browse the tables in each database. Obviously this is not meant to
> happen - one of the databases should be in Restoring mode.Transactions
> are only going into the one database - on the Primary server. Has
> anyone come across this before? Any ideas on how to proceed?
> If I attempt to pause mirroring on the primary server it fails with
> the message "The database mirror is busy, re-issue the command later".
> I can see this getting really bad if I am unable to (at worst) remove
> mirroring from either database due to them thinking that they are the
> principal and cannot connect to the other mirrored database.
> (both servers on SP2.)
My plan was to come in and backup both databases and then restart the
SQL Service on the secondary, hoping that the state would change.
However when I got in to check this (maybe 6 hours later), the
database on the Secondary had elevated itself to be the Principal
(connected) and the Primay server's database was Synchronised/
Restoring. Good. I have since manually failed the databases back over
to primary. All seems ok so far but I will need to check this
throughout the day. (Hope no-one else runs into this!)

Mirroring between Standard and Enterprise editions...

Hi All,

We are going to use database mirroring and database snapshots for reporting. The principal server runs SQL Server 2005 Standard Edition and mirror (reporting) runs SQL Server 2005 Enterprise Edition (64-bit). I failed to setup database mirroring trough GUI using SQL Server Management Studio and got this error:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

SQL BOL says that one of requirements for database mirroring is to use the same SQL Server editions but I am wondering if it works trough T-SQL why MS mention it in the requirements list, also is totally unsupported by MS?

Thanks

Oleg

Oleg,

While this will probably work fine, we don't test this combination internally. If you mirror from a principal that is STD edition to a mirror that is EE edition, it would probably work without issues. Mirroring the other way (from EE to STD) could be problematic because of the EE features that are not present in STD. Since mirroring supports failover and failback, in theory it is possible for you to be in the situation where you are mirroring from EE->STD edition even if that is not the initial configuarion.

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks for your answer.

Our plan is to use database mirroring only for reporting and not for high availability. (We use replication and remote DC for HA). So mirroring from Standard to Enterprise Edition will be the only one scenario that we going to use option in our reporting environment.
The concern that we have is if we will be able to use mirroring between Standard and Enterprise in the future releases of SP2, SP3.

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

Yes we are going to use EE edition only for reporting since it doesn't require extra support efforts and also we can have a fair data refresh latency.
For the stand-by server you don't have to pay for licensing and it's valid for cluster or mirroring unless you also going to use it also for reporting.

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Mirroring between Standard and Enterprise editions...

Hi All,

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

Thanks

Oleg

Oleg,

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Mirroring and Replication

I'm using replication and want to mirror my publisher to get high availability. As I understand the documentation the principal and the mirror must both use the same distributor instance. So if the distributor fails the replication fails. This seem like a big weakness. Maybe I'm missing some thing. Is there a way to set it up so that replication continues if a distributor fails -- some mechanism that will switch in a "back-up" distributor? I'm looking for a configuration that will continue to run in the event of any single failure. This means that both the publisher and the distributor must be covered by mechanisms that keeps them going in the event of a failure. Mirroring covers the failure of the publisher but I don't see a mechanism that will conver the failure of the distributor.

My application is developed in .net / c#. Data is accessed through .net sql server data provider etc.

The only solution right now is clustering, there's way too hard-coded server names at the publisher/distributor that makes it difficult to get mirroring to work. We're looking at developing other solutions, but they won't be available until after the next SQL Server release. But for now, clustering is your answer.

Wednesday, March 28, 2012

Mirroring - High Safety Mode

I'm testing DB mirroring in high safety mode without a witness server. When
I try to simulate a complete failure of the principal server (I stop the
principal SQL Server service) I can't get the mirrored DB's to come online.
The status is stuck in "mirror, disconnected/in recovery"
I don't have any errors in the event log other than an event ID 1474, SQL
communication error with the principal server.
Do I need a witness server ? CAN I GET A WITNESS ! ?

Nick
I added a witness server and this resolved my problem.
"Nick" <larosan@.yahoo.com> wrote in message
news:udadnef-_oVQGt3bnZ2dnUVZ_uKknZ2d@.comcast.com...
> I'm testing DB mirroring in high safety mode without a witness server.
> When I try to simulate a complete failure of the principal server (I stop
> the principal SQL Server service) I can't get the mirrored DB's to come
> online. The status is stuck in "mirror, disconnected/in recovery"
> I don't have any errors in the event log other than an event ID 1474, SQL
> communication error with the principal server.
> Do I need a witness server ? CAN I GET A WITNESS ! ?

> Nick
>
>
>
sql

Mirroring

Is there a way to detect if the principal or mirror is running the database?
I want build my own altert system, sending an SMS whenever a automatic
failover/failback happens.
Per Schjetne"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
You can set alerts on the messages in the event log. Talk to your monitoring
application vendor for more info.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||select * from sys.database_mirroring
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
> Per Schjetne
>|||The sys.dm_database_mirroring view will give you the state information that
you
need to monitor. You will need to access this on both partners.
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.
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
> Per Schjetne
>

Mirroring

Is there a way to detect if the principal or mirror is running the database?
I want build my own altert system, sending an SMS whenever a automatic
failover/failback happens.
Per Schjetne"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
You can set alerts on the messages in the event log. Talk to your monitoring
application vendor for more info.
--
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||select * from sys.database_mirroring
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
> Per Schjetne
>|||The sys.dm_database_mirroring view will give you the state information that
you
need to monitor. You will need to access this on both partners.
--
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.
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:%23V%23sM6caGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Is there a way to detect if the principal or mirror is running the
> database? I want build my own altert system, sending an SMS whenever a
> automatic failover/failback happens.
> Per Schjetne
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:

> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:[vbcol=seagreen]
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
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
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:
> > We are upgrading to 2005 and have decided to go to a mirrored 2005
> > environment. We have picked out the production server (principal) 16
> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> > having difficulty finding out how "beefy" a box we need for the Witness
> > server. Does anyone have any experience with this and can you assist me
> > in specs. I am working with our Network Administrator.
> >
> > Thanks for all the help!
> >
> > Kristina|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
--
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
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>> Hi,
>> Kristina - your witness server can be a single processor box with
>> 1/2 GB memory. You can even use SQL Server express. The resources
>> needed for the witness server is minimum.
>> This from our Microsoft pre-sales rep.
>> hth
>>
>> KristinaDBA@.gmail.com wrote:
>> > We are upgrading to 2005 and have decided to go to a mirrored 2005
>> > environment. We have picked out the production server (principal) 16
>> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
>> > having difficulty finding out how "beefy" a box we need for the Witness
>> > server. Does anyone have any experience with this and can you assist me
>> > in specs. I am working with our Network Administrator.
>> >
>> > Thanks for all the help!
>> >
>> > Kristina
>

Mirror Status Change

How can I determine when the status of a mirrored database on a server
changes from Principal to Mirrored?
Also, can I determine who or what caused the status to change?
Hi
SELECT * FROM sys.database_mirroring;
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:49F51B2A-1AA4-4BF5-ADC4-2C5C2E629FB4@.microsoft.com...
> How can I determine when the status of a mirrored database on a server
> changes from Principal to Mirrored?
> Also, can I determine who or what caused the status to change?
|||This shows the current status, but not who, what, when, how, or why it changed.
"Uri Dimant" wrote:

> Hi
> SELECT * FROM sys.database_mirroring;
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:49F51B2A-1AA4-4BF5-ADC4-2C5C2E629FB4@.microsoft.com...
>
>
|||You can see information about the database mirroring state change in the
error log.
Database mirroring is active with database 'Database1' as the principal copy.
Database mirroring is active with database 'Database1' as the mirror copy.
Another option is to capture the Database Mirroring State Change Event Class
with SQL Trace or Event Notifications.
http://msdn2.microsoft.com/en-us/library/ms191502.aspx
Ola Hallengren
http://ola.hallengren.com
"BigSam" wrote:
[vbcol=seagreen]
> This shows the current status, but not who, what, when, how, or why it changed.
> "Uri Dimant" wrote:

Mirror Status Change

How can I determine when the status of a mirrored database on a server
changes from Principal to Mirrored?
Also, can I determine who or what caused the status to change?Hi
SELECT * FROM sys.database_mirroring;
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:49F51B2A-1AA4-4BF5-ADC4-2C5C2E629FB4@.microsoft.com...
> How can I determine when the status of a mirrored database on a server
> changes from Principal to Mirrored?
> Also, can I determine who or what caused the status to change?|||This shows the current status, but not who, what, when, how, or why it changed.
"Uri Dimant" wrote:
> Hi
> SELECT * FROM sys.database_mirroring;
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:49F51B2A-1AA4-4BF5-ADC4-2C5C2E629FB4@.microsoft.com...
> > How can I determine when the status of a mirrored database on a server
> > changes from Principal to Mirrored?
> > Also, can I determine who or what caused the status to change?
>
>|||You can see information about the database mirroring state change in the
error log.
Database mirroring is active with database 'Database1' as the principal copy.
Database mirroring is active with database 'Database1' as the mirror copy.
Another option is to capture the Database Mirroring State Change Event Class
with SQL Trace or Event Notifications.
http://msdn2.microsoft.com/en-us/library/ms191502.aspx
Ola Hallengren
http://ola.hallengren.com
"BigSam" wrote:
> This shows the current status, but not who, what, when, how, or why it changed.
> "Uri Dimant" wrote:
> > Hi
> >
> > SELECT * FROM sys.database_mirroring;
> >
> >
> >
> > "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> > news:49F51B2A-1AA4-4BF5-ADC4-2C5C2E629FB4@.microsoft.com...
> > > How can I determine when the status of a mirrored database on a server
> > > changes from Principal to Mirrored?
> > > Also, can I determine who or what caused the status to change?
> >
> >
> >sql

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola