Showing posts with label mirrored. Show all posts
Showing posts with label mirrored. Show all posts

Tuesday, March 27, 2012

database mirroring using certificate? (two principles and one mirror)

Hi, all.

here is the situation.

we designated one server as a mirrored server. this server will host all mirrored databases from different principles in different server using certificate.

1) Server A (Principle) Server B(Mirrored)

2) Server C (Principle) Server B(Mirrored)

I was able to set up mirroring between A and B. But unable to set up mirroring between C and B.

My question is "Can I grant connect on endpoint to two different host logins?"

For example, on the server B

I created two different logins for Server A and C in order to assign different certificates to two principle servers.

For Server A.

create login server_A_login with password = 'djkkajnxks';

go

create user server_A_user for login server_A_login

go

create certificate server_A_cert

authorization Server_A_user

from file = 'c:\certificate\server_a_cert.cer'

go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_a_login];
GO

For Server C.

create login server_C_login with password = 'djkkajnxks';

go

create user server_C_user for login server_c_login

go

create certificate server_c_cert

authorization Server_c_user

from file = 'c:\certificate\server_c_cert.cer'

go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_c_login];
GO

when I set up like this and try to start mirroring, I am getting error from server c saying, "the network tcp:\serverb.corp.com can not be reached or does not exist"

But mirroring between server a and b is OK. I set up them first, then between c and b.

any ideas?

thanks,

Try to backup the cert from A and put into C, make sure the thumbprint version of the certificate are same. Try to execute SELECT * from sys.certificates to view the thumbprint information. Hope this will help you.

Best Regards,

Hans1982

|||Review the similar information in SQL 2005 BOL http://msdn2.microsoft.com/en-us/ms178578.aspx and additional information http://msdn2.microsoft.com/en-us/ms191140.aspx fyi.

Thursday, March 22, 2012

Database mirroring and automatic failover

Hi,
I need some advice on database mirroring. We have our databases
mirrored successfully in a test envirnment using High Availiblity and
Automatic Failover. I have alerts configured for transaction delays
and mirroring changes etc, but we are not sure how to best handle the
production reboots. For example, we schedule reboots on the principal
server weekly - I assume this will cause automatic failover as long as
it exceeds my timeout period. Is the suspend, resume command the best
way to handle this or do I have other options? I was thinking about
scheduling a job to run these commands during the reboot. I don't want
to set the timeout for more than 30 seconds.
ALTER DATABASE DB SET PARTNER SUSPEND
--after suspension, resume
ALTER
DATABASE DB SET PARTNER RESUME
Is this the proper way to proceed? Anyone with experience with this
issue out there or something similar?
Thanks,
KristinaHi Kristina
Why do you need to schedule or to do weekly reboots on a system configured
for high availability?
Ben Nevarez
"Kristina" wrote:
> Hi,
> I need some advice on database mirroring. We have our databases
> mirrored successfully in a test envirnment using High Availiblity and
> Automatic Failover. I have alerts configured for transaction delays
> and mirroring changes etc, but we are not sure how to best handle the
> production reboots. For example, we schedule reboots on the principal
> server weekly - I assume this will cause automatic failover as long as
> it exceeds my timeout period. Is the suspend, resume command the best
> way to handle this or do I have other options? I was thinking about
> scheduling a job to run these commands during the reboot. I don't want
> to set the timeout for more than 30 seconds.
>
> ALTER DATABASE DB SET PARTNER SUSPEND
> --after suspension, resume
> ALTER
> DATABASE DB SET PARTNER RESUME
> Is this the proper way to proceed? Anyone with experience with this
> issue out there or something similar?
> Thanks,
> Kristina
>|||On Jan 8, 8:59=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi Kristina
> Why do you need to schedule or to do weekly reboots on a system configured=
> for high availability?
> Ben Nevarez
>
> "Kristina" wrote:
> > Hi,
> > I need some advice on database mirroring. We have our databases
> > mirrored successfully in a test envirnment using High Availiblity and
> > Automatic Failover. I have alerts configured for transaction delays
> > and mirroring changes etc, but we are not sure how to best handle the
> > production reboots. For example, we schedule reboots on the principal
> > server weekly - I assume this will cause automatic failover as long as
> > it exceeds my timeout period. Is the suspend, resume command the best
> > way to handle this or do I have other options? I was thinking about
> > scheduling a job to run these commands during the reboot. I don't want
> > to set the timeout for more than 30 seconds.
> > ALTER DATABASE DB SET PARTNER SUSPEND
> > --after suspension, resume
> > ALTER
> > DATABASE DB SET PARTNER RESUME
> > Is this the proper way to proceed? Anyone with experience with this
> > issue out there or something similar?
> > Thanks,
> > Kristina- Hide quoted text -
> - Show quoted text -
Ben,
Basically these are scheduled updates from Microsoft.
Do you have any ideas around this?
Kristina|||If you are configuring a database server with high availability you should
not have weekly reboots for Windows updates. Perhaps you can apply those
updates every one or two months, and just pay special attention to security
updates.
When you reboot the principal server the database will fail over to the
mirror, which becomes the new principal. When the server is back online it
will become the new mirror. All of this is automatic with no DBA
intervention. So I do not understand why you want to change the timeout or
the status to SUSPENDED.
Ben Nevarez
"Kristina" wrote:
> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Hi Kristina
> >
> > Why do you need to schedule or to do weekly reboots on a system configured
> > for high availability?
> >
> > Ben Nevarez
> >
> >
> >
> > "Kristina" wrote:
> > > Hi,
> >
> > > I need some advice on database mirroring. We have our databases
> > > mirrored successfully in a test envirnment using High Availiblity and
> > > Automatic Failover. I have alerts configured for transaction delays
> > > and mirroring changes etc, but we are not sure how to best handle the
> > > production reboots. For example, we schedule reboots on the principal
> > > server weekly - I assume this will cause automatic failover as long as
> > > it exceeds my timeout period. Is the suspend, resume command the best
> > > way to handle this or do I have other options? I was thinking about
> > > scheduling a job to run these commands during the reboot. I don't want
> > > to set the timeout for more than 30 seconds.
> >
> > > ALTER DATABASE DB SET PARTNER SUSPEND
> > > --after suspension, resume
> > > ALTER
> > > DATABASE DB SET PARTNER RESUME
> >
> > > Is this the proper way to proceed? Anyone with experience with this
> > > issue out there or something similar?
> >
> > > Thanks,
> > > Kristina- Hide quoted text -
> >
> > - Show quoted text -
> Ben,
> Basically these are scheduled updates from Microsoft.
> Do you have any ideas around this?
> Kristina
>|||"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
>Ben,
>Basically these are scheduled updates from Microsoft.
>Do you have any ideas around this?
>Kristina
Yes, don't apply them.
Heresy you say? Sort of.
For a production high availability DB I would schedule these infrequently
and only after OTHER people have tested them on their servers. If it's a
supercritical patch, I might rush it on, but typically I'm very conservative
about applying patches, especially to production systems that are already
well controlled by other methods.
For example, simply not permitting any outside access to port 1433 will
greatly increase your safety. If the outside world can't get to it, doesn't
matter if say a buffer overflow exists (i.e. Code Red).
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Jan 9, 10:53=A0pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> >Ben,
> >Basically these are scheduled updates from Microsoft.
> >Do you have any ideas around this?
> >Kristina
> Yes, don't apply them.
> Heresy you say? =A0Sort of.
> For a production high availability DB I would schedule these infrequently
> and only after OTHER people have tested them on their servers. =A0If it's =a
> supercritical patch, I might rush it on, but typically I'm very conservati=ve
> about applying patches, especially to production systems that are already
> well controlled by other methods.
> For example, simply not permitting any outside access to port 1433 will
> greatly increase your safety. =A0If the outside world can't get to it, doe=sn't
> matter if say a buffer overflow exists (i.e. Code Red).
> --
> Greg Moore
> SQL Server DBA Consulting =A0 =A0 =A0 =A0 =A0 Remote and Onsite available!=
> Email: sql =A0(at) =A0greenms.com =A0 =A0 =A0 =A0 =A0http://www.greenms.co=
m/sqlserver.html
Thanks for all the advice guys. I am not in charge of the servers and
the patches in my current envirnment. I will be using this information
to convice the networking team to not schedule the reboots as
frequently. If they do, I will just create a job to suspend and then
re-initiaize the mirroring during the period that the server is down. I

Database Mirroring & Replication Considerations

Hi all,

Can a publisher be mirrored? What are the implications, issues, gotchas? Transactional, Merge or Transactional w/ Updating Subscribers is what I'm considering.

Bottom line is I would like to use mirroring, but only one mirror will not suffice.

Thank you in advance.

Ray Nichols

I am using this configuration

Server 1 - data 1 - DR (Mirroring)
Server 1 - Data 1 - Publisher (Replication - Transactional)

also you could refer http://msdn2.microsoft.com/en-us/library/ms151799.aspxsql

Database Mirroring - Multiple Endpoints

Hi All
Is it possible to create multiple endpoints for Database Mirroring in SQL
Server 2005?
After reading that only 10 databases should be mirrored per endpoint I tried
to create another endpoint and received the following error:
An endpoint already exists with the bindings specified. Only one endpoint
supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
endpoint and execute the CREATE ENDPOINT statement.
Can someone please clarify how to create an additional endpoint and/or how
to mirror more than 10 databases.
Thanks
Can you point me to the document which talks about 10 mirrored database per
endpoint.
I have more than 10 endpoints in my server.
Thank you
"David" <David@.discussions.microsoft.com> wrote in message
news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I
> tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks
|||Hi David, please take a look at the below link:
http://www.sqlcommunity.com/default.aspx?tabid=77&id=79
All mirroring connections on a server instance use a single database
mirroring endpoint.
There's no hard limit on ho many databases can be mirrored per server. This
depends on the resources available on the server. The best practice however
is to limit your SQL Server with 10 but this is not an official limit.
You may explore more about database mirroring @. Database Mirroring clinic on
www.sqlcommunity.com (Below is the link)
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"David" wrote:

> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks
|||Hi Shailesh, there's no limit on how many databases you can mirror per
server. This depends on the availability of the resorces on the server.
However, the best practice hs been to limit your server with 10.
More info on database mirroring can be found at Database Mirroring Clinic on
www.sqlcommunity.com :
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"Shailesh Khanal" wrote:

> Can you point me to the document which talks about 10 mirrored database per
> endpoint.
> I have more than 10 endpoints in my server.
> Thank you
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
>
>

Database Mirroring - Multiple Endpoints

Hi All
Is it possible to create multiple endpoints for Database Mirroring in SQL
Server 2005?
After reading that only 10 databases should be mirrored per endpoint I tried
to create another endpoint and received the following error:
An endpoint already exists with the bindings specified. Only one endpoint
supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
endpoint and execute the CREATE ENDPOINT statement.
Can someone please clarify how to create an additional endpoint and/or how
to mirror more than 10 databases.
ThanksCan you point me to the document which talks about 10 mirrored database per
endpoint.
I have more than 10 endpoints in my server.
Thank you
"David" <David@.discussions.microsoft.com> wrote in message
news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I
> tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks|||Hi David, please take a look at the below link:
http://www.sqlcommunity.com/default.aspx?tabid=77&id=79
All mirroring connections on a server instance use a single database
mirroring endpoint.
There's no hard limit on ho many databases can be mirrored per server. This
depends on the resources available on the server. The best practice however
is to limit your SQL Server with 10 but this is not an official limit.
You may explore more about database mirroring @. Database Mirroring clinic on
www.sqlcommunity.com (Below is the link)
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"David" wrote:
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks|||Hi Shailesh, there's no limit on how many databases you can mirror per
server. This depends on the availability of the resorces on the server.
However, the best practice hs been to limit your server with 10.
More info on database mirroring can be found at Database Mirroring Clinic on
www.sqlcommunity.com :
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"Shailesh Khanal" wrote:
> Can you point me to the document which talks about 10 mirrored database per
> endpoint.
> I have more than 10 endpoints in my server.
> Thank you
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> > Hi All
> >
> > Is it possible to create multiple endpoints for Database Mirroring in SQL
> > Server 2005?
> >
> > After reading that only 10 databases should be mirrored per endpoint I
> > tried
> > to create another endpoint and received the following error:
> >
> > An endpoint already exists with the bindings specified. Only one endpoint
> > supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> > endpoint and execute the CREATE ENDPOINT statement.
> >
> > Can someone please clarify how to create an additional endpoint and/or how
> > to mirror more than 10 databases.
> >
> > Thanks
>
>

Database mirroring

Hi All

I need to find out on limitation in SQL 2005 standered edition on numbers of databases can be mirrored, in other words whats the maximum numbers of databases can be used for database mirroring

Thanks

Alps

I believe that there is no limitation on using databases in database mirroring.

More info :

Database Mirroring in SQL Server 2005.

Thanks.

Naras.

|||As explained there is not such limit, but on 1 server it is not advisable to have more than 10 database that is based on the tests performed on a high-end spec. hardware. Also when it comes to recover in case of any recovery it will take more time to recover and in this case based on your requirement you might look at to restrict upto 5 database per instance.