EMC RecoverPoint and SQL Server

  • We are trying to implement EMC RecoverPoint with SQL clustering.

    What we had to do at the DR side after syncing the databases was detach the databases and take the clustered disk resources off-line so replication could happen at the block-level. However when we brought the DR side back on-line we had to manually attach the databases.

    My question is - is this the right way of doing it as ideally we want to have this automated and not have to manually attach the databases t the DR side.

    Thanks.

  • You should engage EMC for that. They should have some guidelines on the best way to do this with their product.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • After some more research it seems the suggested way is to manually detach the databases then reattach manually in a DR scenario.

  • Can you provide some more information on your Recoverpoint automation? We're starting to work with RecoverPoint as well, though without clustering, so our scenario is a little simpler (mount to a point in time with Recoverpoint, then bring up SQL at the remote site).

  • I'll definitely be interested in hearing about your experience with RecoverPoint. We started using it a few months back to replicate data to our remote DR site. Our first DR test worked fine but the second one not so much. We had a few databases come up in suspect mode as well as some table errors in other databases (DBCC reported). We are still trying to figure out what went wrong.

  • Guys,

    We tested our DR strategy using RecoverPoint.

    It was a disaster. Most critical databases did not attach.

    Msg 1813, Level 16, State 2, Line 2

    Could not open new database 'DRTest'. CREATE DATABASE is aborted.

    Msg 823, Level 24, State 2, Line 2

    The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00001b8ccf4000 in file 'O:\SQLDATA\DRTest.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Msg 3313, Level 21, State 2, Line 2

    During redoing of a logged operation in database 'DRTest', an error occurred at log record ID (378200:49915:21). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

    Are there any pointers or suggestions for doing recoverpoint DR strategy right?

  • Hi. I realize this thread is someone old but I was wondering if anyone had better experiences with using RP?

    My own experience has been spotty. I have been able to attach databases to our DR SQL Server (2008 R2) using a snapshot from RP. I've had success in some instances but other times, there were failures due to one of the storage becoming inaccessible. EMC did not have any real solution to our problem except to upgrade to a more recent version of RP - which we plan on doing.

    Furthermore, they do not go beyond the storage aspect and into the required steps for within SQL Server. So, I've had to discover on my own the required steps needed to use RP on our DR server.

    So what we have come up with so far are the following for mounting/unmounting RP storage on DR. I can't say it's perfect but it seems to work for the most part.

    Mount RP storage:

    1. Disable replication and mount snapshot from RP to the DR host

    2. Online the disks in Disk Management

    3. Start SQL services and attach databases

    4. Create and/or fix orphaned or missing db logins.

    Unmount RP storage (to reenable replication):

    1. Detach databases from SQL Server in DR and stop SQL Services.

    2. Offline the disks used in Disk Management.

    3. Unmount RP Storage and enable replication in RP.

    Something I have yet to test is to see what happens if we do NOT detach the databases and return the disks back to RP. Would I be able to take another snapshot and mount the storage and start sql services without issues? OR will it cause problems because I didn't detach during the unmount steps?

  • Which database and log files are being replicated?

    Was the replication up to date on all of them, or was it behind on some of the LUNs?

    In our testing, we put every .mdf and .ldf except for tempdb on to SAN luns - including master, model, and msdb.

    We carefully set up SQL Server on the DR site to point to where those would be mounted, and left SQL shut down on the DR side.

    Thus, we first mount with Recoverpoint and make sure the LUNs are attached, online the LUNs, rescan them, make sure the drive letters are what's expected and are all presenting, and only then do we try starting SQL Server.

    It's a little more complex than that, but that's the basics. It's critical to make sure every drive is mounted properly and is available at the expected drive letter before starting SQL Server.

    ETA: Using the above technique, there's no reason whatsoever to detach or attach databases within SQL Server; they come as a complete set, from Master on down (except TempDB, which you simply need to have storage at the correct drive letter/mount point for). Remember, RecoverPoint is a very simplistic block level replication; it knows nothing about databases, nothing about files, not even anything about file systems.

  • Hmm.. we only have the user databases and logs on RP. How did you install SQL Server on your DR without creating the system databases?

  • We installed standalone sql servers with system dbs at DR.

    We created jobs on Prod servers to script logins, permissions, jobs etc and robocopy scripts to the relevent sql servers at DR.

    Data and log drives are repliacted to DR real time.

    We have created a schedule fo DR now and every 1 months we sync up all releases and changes.

    During that time, we ask out storage team to present images at DR servers. Once the replicated drives are available, we attach the dbs and run the latest permissions, jobs scripts.

    Middleware team deploy all the releases, QA does spot testing and we declare 'all is well' at teh end of the day feeling good that we have some plan. 🙂

    We had some bumpy start with Recovery Point. See my previous message on this thread. Basically, when we get that message questioning integrity, we go contunue going back to previous images until we find the one that works. We communicated to Complianec team at work that even though target is to have no data loss in case of disaster, this situation could cause some data loss - not major since image is taken almost every few seconds.

  • JamesK1 (5/9/2013)


    Hmm.. we only have the user databases and logs on RP. How did you install SQL Server on your DR without creating the system databases?

    Think of RecoverPoint's presented LUNs as a removeable platter or floppy or USB thumb drive or USB/Firewire/eSATA external drive - whatever you're comfortable with. It's storage that you assign a drive letter to.

    Think of the drives/virtual disks/partitions/logical drives/etc. on your system the same way - it's harder to move them, but they're still storage you assign a drive letter to.

    Think of SQL as something that just looks for drive letters - it really doesn't care what storage is attached, it's going to blindly look for what it expects.

    First SQL looks for the Master database and the ERRORLOG file - that's why those command line parameters are set in SQL Server Configuration Manager, under the properties of the service, Advanced tab.

    Then SQL reads from the Master database to find everything else.

    Thus, the trick with Recoverpoint is, ideally, to have 100% identical drive letters and paths for all .mdf and .ldf files on Primary and DR (including tempdb! We just don't both replicating it, since it's "fresh" on each startup) before we start SQL Services.

    Realize that you can always change drive letter assignments in diskmgmt.msc!

    X: is whatever drive letter and path your production database has its system database datafiles (and maybe the logs)

    Y: is whatever drive letter and path your production database has its system database logs (if they're in a different location from the datafiles)

    A: is some unused letter on DR

    B: is some other unused letter on DR (if Y: is in use)

    So, what you do (OPTIONAL separation of Master log and database files, X: for data and possible logs, Y: for possible logs) [EXAMPLE NOT TO SCALE - this is pseudocode]:

    1) Take some local or external storage, and assign it to X: (and Y:), where your Master database (and Master log) will go.

    2) You install SQL such that your system DB's are on X: (and their logs on Y:).

    2a) Alternately, move your system DB's to X: (and their logs to Y:) using the normal methods for moving system DB's.

    2b) make sure ERRORLOG is on the same drive letter and path on Primary and DR, too; replicate or not at your option (I would, to track errors that happened during or prior to disasters!).

    3) That's your "local install" - useful for doing patching, trivial function checks, and so on.

    4) Stop SQL on DR

    5) Now, change X: to A:, and if need be, Y: to B:, by changing the drive letter

    6) Mount Recoverpoint LUNs from primary to DR, making sure on DR they get the same drive letters.

    7) Start SQL (NOTE: the fine details may not be quite right, particularly the order - if anyone knows the internals and would like to correct this, that would be great! The general process, however, must look a lot like this)

    7a) SQL looks for ERRORLOG, and finds it where it expects, because both Primary and DR are set up with identical paths and filenames ERRORLOG

    7b) SQL looks for Master on X:

    7c) SQL finds Master where it expects, because both Primary and DR are set up with identical paths and filenames for Master .mdf and .ldf files

    7d) SQL reads Master to see where other databases are

    7e) SQL starts tempdb in the path specified; which is can, since that path exists on both Primary and DR

    7f) SQL looks for other database locations from Master

    7g) SQL finds the other databases where it expects, because both Primary and DR are set up with identical paths and filenames for other database.mdf and .ldf files!

    WARNING: ALL YOUR JOBS ARE PRESENT AND READY!!! ALL YOUR LINKED SERVERS ARE PRESENT AND READY!!! DO NOT START SQL SERVER AGENT UNLESS YOU REALLY, REALLY NEED TO!!!! If you start SQL Server Agent right off, then if you have a job that does production work like outputting a file or deleting backups or sending an email to a client, it's going to try to do that, because it thinks it is production!!! This could be a serious, serious problem if you're running tests!

  • It sounds like we do the same things as you Aashini but with less scripting.

    I'm interested in how Nadrek has his setup as it would be great to not have to do all the extra work of configuring the security and jobs.

  • Thanks for the info Nadrek. I figured it might be a drive remapping trick. It does sound like a very good setup except for the last part about the SQL Agent for we do use our DR for testing on occasion and wouldn't want production processing going on from DR.

    It's good to know for sure. Thanks!

  • Can you elaborate on the SQL Server installation at the DR Site please. I'm still not sure how to install SQL Server at the DR site before presenting the recoverpoint LUNs to the DR Database Server. Thank You!

    "We installed standalone sql servers with system dbs "

  • Shawn Scoville (7/15/2014)


    Can you elaborate on the SQL Server installation at the DR Site please. I'm still not sure how to install SQL Server at the DR site before presenting the recoverpoint LUNs to the DR Database Server. Thank You!

    "We installed standalone sql servers with system dbs "

    That's steps 1-4 in my post; essentially, you just install SQL Server at DR with the same options you chose at the main site, and then patch it to exactly the same level as your main site.

    Make sure the system database and log files (and error files) are all installed to drive letters that you can reassign to the Recoverpoint LUNs

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply