SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jason Shadonix / SQL Server 2008 Mirroring Testing / Latest Posts

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Mon, 26 Apr 2010 08:22:11 GMT

[quote][b]dave-dj (4/26/2010)[/b][hr]Hi Jason,The last post dissappointingly answered a question I had. My other question though, is what process would you recommend for upgrading SQL 2005 servers that are taking part in mirroring.[/quote]I haven't had to do that yet (all of our SQL 2K8 servers are supporting new apps, and we don't have plans to upgrade our existing 2K5 servers any time soon). I would think you would want to upgrade the mirror server first if you want to have a minimal impact on mirroring. (You can mirror from 2005-2008 for awhile if you need to).

RE: SQL Server 2008 Mirroring Testing

dave-dj — Mon, 26 Apr 2010 03:12:06 GMT

Hi Jason,The last post dissappointingly answered a question I had. My other question though, is what process would you recommend for upgrading SQL 2005 servers that are taking part in mirroring.

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Mon, 20 Apr 2009 12:14:50 GMT

No. You won't be able to restore your 2008 principal backup on your 2005 mirror server.

RE: SQL Server 2008 Mirroring Testing

paul.t.silvey — Mon, 20 Apr 2009 12:08:54 GMT

Can I set up a SQL Server 2008 principal to a 2005 mirror? Maybe run the 2008 in backwards compatibility?Thoughts anyone?

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Sun, 01 Feb 2009 11:04:23 GMT

Glad you got it working. Just keep in mind two things about mirroring from a 2005 principal to a 2008 mirror:1) You can't do snapshots2) If for some reason your principal dies and you fail over to the 2008 mirror instance and make that the principal, you've just upgraded your database to SQL 2008 and can't restore it to SQL 2005 ever again. (OK, I suppose if you really wanted to you could manually extract all the data, or use one of the data transfer wizards, but it probably wouldn't be fun if your DB is very big).

RE: SQL Server 2008 Mirroring Testing

mikes067 — Fri, 30 Jan 2009 18:01:19 GMT

it worked fine using 2008. I connected to both and setup the mirror(2005 to 2008).I am fully patched on 2008 so maybe thats your problem. Worked flawless. Monitor in 2005 obviously doesnt like it though but it see's it.You have to truly monitor from 2008

RE: SQL Server 2008 Mirroring Testing

venablc — Fri, 30 Jan 2009 14:49:53 GMT

I haven't experiencince mirroring between 2005 and 2008 (just 2005 to 2005) however i did find the GUI pretty buggy and would recoomend doing it "manually" where possible.

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Fri, 30 Jan 2009 10:23:43 GMT

If by manually you mean by scripting it instead of using the GUI setup in SSMS, then yes, I did it manually. I haven't ever played with the GUI, so I can't give you much advice there if it's giving you trouble. Other than I would suggest not mirroring from 2005 to 2008 unless you have plans to upgrade your 2005 box in the near future.

RE: SQL Server 2008 Mirroring Testing

mikes067 — Fri, 30 Jan 2009 10:12:06 GMT

How did you implement 2005(principle) to 2008(Mirror)... It won't let me configure the security?I have to manually create the endpoint on 2008? Then use that?

RE: SQL Server 2008 Mirroring Testing

venablc — Thu, 11 Dec 2008 08:56:56 GMT

You correct, i never realised...Snapshots aren't standalone copies of the database, they appear much more complicated and still require the use of the original MDF file. For this reason they cannot be used on any other physical server than the one they were created on.As long as physical resources such as processor power are not a problem you can however attach the snashot to a new database on the same server which may yield performance improvements in regards to locking.I haven't had time to read them properly myself but here are a few links which look like they explain how it works and what they can be used for quite well...http://msdn.microsoft.com/en-us/library/ms187054(SQL.90).aspxhttp://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1176142,00.html

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Wed, 10 Dec 2008 10:20:05 GMT

When mirroring, you can take snapshots of your database, but you can't backup or restore a snapshot, or have the snapshot on a different server than the server you are mirroring to. Unless someone knows a trick I don't know of (which is very possible).

RE: SQL Server 2008 Mirroring Testing

ian-596241 — Wed, 10 Dec 2008 10:13:39 GMT

our database is copied via a snapshot replication then pushed to backup sql server. That's the bit that is time-consuming - it takes several hours to complete unfortunately, but does run without any manual intervention to leave a reporting database at a maximum of 24 hours out of date, which is OK for most reports.

RE: SQL Server 2008 Mirroring Testing

venablc — Wed, 10 Dec 2008 10:08:36 GMT

I haven't tryed the snaphost/restore method but i would imagin it is just a case of restoring the snapshot as you would a database backup

RE: SQL Server 2008 Mirroring Testing

Jason Shadonix — Wed, 10 Dec 2008 09:44:08 GMT

[quote][b]venablc (12/10/2008)[/b][hr]You can take snapshots of a mirror database and then restore these onto a live reporting database (although not if you are using sql2008 as your mirror database according to this article) however this could be processor and hard-drive intensive. It would also mean your reporting would not be live. If this was not a problem then you could automate a snapshot from the mirror database onto your reporting database nightly for example.[/quote]Once you take a snapshot of a mirrored database on the mirror server, how are you restoring that snapshot onto another server?

RE: SQL Server 2008 Mirroring Testing

Andrew Peterson-472853 — Wed, 10 Dec 2008 09:34:18 GMT

Jason,Good stuff, and thanks for taking the time to research and write. This adds to the collective knowledge!

RE: SQL Server 2008 Mirroring Testing

venablc — Wed, 10 Dec 2008 07:31:51 GMT

What is time consuming?

RE: SQL Server 2008 Mirroring Testing

ian-596241 — Wed, 10 Dec 2008 07:10:35 GMT

I thought this was the case. I've gone down the replication route too but it is quite time-consuming for a large database. It would be great if this mirroring technology could also leave an up-to-date copy of the live database that could be used for reporting. Oh well, never mind! txtPost_CommentEmoticon(':)');

RE: SQL Server 2008 Mirroring Testing

venablc — Wed, 10 Dec 2008 05:23:31 GMT

No it can't.The mirrored database remains in a state of "restoring" until it is brought into service through either an automatic or a manual failover from the principle database. The database is unusable while it is in a restoring state.You can take snapshots of a mirror database and then restore these onto a live reporting database (although not if you are using sql2008 as your mirror database according to this article) however this could be processor and hard-drive intensive. It would also mean your reporting would not be live. If this was not a problem then you could automate a snapshot from the mirror database onto your reporting database nightly for example.We are also looking to seperate our "functional" database activity from our "reporting" database activity. Instead of taking the snapshot approach we are looking to use database replication so we will have a live copy of our database which we can use for reporting.

RE: SQL Server 2008 Mirroring Testing

ian-596241 — Wed, 10 Dec 2008 02:52:01 GMT

Hi, so can Mirroring be used to keep an up-to-date copy of the database to be user for reporting queries by the web application?

SQL Server 2008 Mirroring Testing

Jason Shadonix — Wed, 10 Dec 2008 00:14:19 GMT

Comments posted to this topic are about the item [B]SQL Server 2008 Mirroring Testing[/B]