April 11, 2011 at 9:55 pm
Comments posted to this topic are about the item Database Mirroring Setup
April 11, 2011 at 10:15 pm
Congrats on writing posting your first article!! I think it's a pretty good first article. There are a few points I'd like to clarify just to make sure the readers are clear.
Mirroring is not supported on different Editions for the Principal and Mirror. Though possible to do so, you lose supportability by using different Editions.
The service accounts for the SQL services should not be running as domain admins ... ever.
The principal and mirror servers do not need to use the same port number on both sides. In fact, there are some circumstances where this isn't even possible.
The role of the endpoint doesn't have to be the same on mirror and principal. Each one has to be in either the "Partner" or "All" role.
The picture at the end does not show that the service account has been granted Connect permissions. In fact, it doesn't show the mirroring endpoint at all. The reason for this is because the service account's permissions are inherited from it's role as sysadmin rather than having been explicitly granted.
Regards,
April 12, 2011 at 12:03 am
I agree with Robert's additional comments. if instances are installed on the same server then mirroring ports will have to be different
April 12, 2011 at 12:19 am
Just to reiterate, All I'm all, good article. Don't let my comments discourage you. Keep writing!!
April 12, 2011 at 2:34 am
A good first article. Yes there are a few minor points that will be picked up on but that's all good, as you only learn about article writing this way! Keep on writing.
James
MCM [@TheSQLPimp]
April 12, 2011 at 2:40 am
Thanks for the article. A quick question:
on a production system, can I do the backup/restore steps without
blocking incoming connections? If this is true, then the transactions
issued after the backup, will be applied to the mirror after synchronization?
Thanks
April 12, 2011 at 3:24 am
Screen shot shows principal /sychronised in both cases. Assumed the second screen shot should say 'mirrored/synchronised'
April 12, 2011 at 5:46 am
First thing, good article Brady.
With many topics, sometimes your going to get a lot of feedback (which is good) on little tidbits which may need "more" feeling out.
Backups can be run at anytime but only from the Principle Servers database. Backups are basically point of time snapshots so the database goes on it's merry way while the backup occurs.
For restoring, in mirror configuration, it's a bit more complicated. Basically you have to break the mirror, do your restore on your principle and then go through the whole process of configuring the mirror again.
Also, to add to your article, sample connection string for using the failover in applications...
connectionString="Network=dbmssocn;server=SQL1;Failover Partner=SQL2;Initial Catalog=YourDB;"
It gives the application the 2 partners in the mirror to connect to if one fails. This is for when you have a witness in the mirror operation, "realtime" failover requires the witness server (which can be a simple express version of SQL)
Also, Mirroring is database specific, any configurations for the actual SQL server (such as jobs, security, etc...) have to be manually managed between the servers.
HTH and keep writing...
Dave
April 12, 2011 at 6:06 am
Thanks David, but my question was different:
If I need to add mirroring on a production system which at the time consists
of a single server (db01), do I have to block transactions before taking
the backup on (db01) and only allow them after the mirroring has been
setup?
Or the mirror server is able to apply any additional transactions that were issued
after the backup was taken on db01 and before the mirror was completely
setup (restore on db02 + mirror)
Thanks
April 12, 2011 at 6:18 am
Hi,
I have one question...
while restoring the log backup on mirror db (db will be in restore mode), will the users can access the db at the same time. Bcos in log shipping users can't access the db while db is in restore mode. So is this rule apply in Mirror db as well....?
Thanks in advance
Masood
April 12, 2011 at 6:41 am
@Everyone
Thanks for all the comments!
@robert Davis
Thanks for the feedback. Coming from a MCM and Author of Pro SQL Server 2008 Mirroring your feedback is very important.
While I agree with all your points this was meant to be a brief, easy overview for the Junior level just to get a grasp on what database mirroring looks like. This should not be a production environment's setup.
@adrian.cunningham
Doh, you are correct. Looks like I added the principal server twice. Attached is what the mirror should look like...
@david-2 Atherton
Thanks for the feedback and adding the connection string for people to use also.
Once configured, the mirror database with be unaccessible (like log shipping)
April 12, 2011 at 7:40 am
OK, now first thing first, I would never condone doing any type of configuration changes without testing (and with this article using VM, it's a good idea to create a test VM environment to do the actual config in the lab first before in production) and full backups of everything before moving forward.
With that said, when your basically changing anything production, you should book the time to be offline just in case (yep, be ready for that 2:00 AM deployment).
You can obviously jump right in and start configuring your production DB to be mirrored but with the items in Brady's article as well as Roberts items, there are many aspects such as connectivity, SQL accounts, ports, firewalls, etc...that can play a part in getting your mirror working correctly and you should have that all documented in your steps (from your testing) prior to configuration to make the process clockwork but IMHO, reality usually jumps in with monkey wrenches and you'll probably be scrambling to do fixes/configurations during the setup.
As Brady mentioned, only the Principle database is active and can be connected to. The Mirror database is basically in a permanent state of recovery (receiving logs from the principle)
Dave
April 12, 2011 at 8:52 am
Thanks David for your reply...
But my requirement is - we have to build new reporting server (db) and we have to implement Mirroring for read-only db and users will use Mirror db as reporting db and generate reports.
So my question is --> is Mirroring a feasible option for reporting db....?
Bcos as per log shipping, users can't use db when db is in restoring mode....so is this apply in Mirroring also....?
Thanks
Massoud
April 12, 2011 at 9:01 am
Replicated databases may be something better for you
April 12, 2011 at 9:01 am
Mirroring is not feasible for this situation as the reporting server will not be able to read the data from the mirrored instance.
Replication or backup/restore options is the best way to accomplish this.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply