Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kotetsu
Starting Member
3 Posts |
 Posted - 2012-04-24 : 17:56:03
|
| I have a conceptual question. I don't have any specific hardware, or anything like this, just some general information.What are the possible ramifications of detaching a LUN containing SQL databases from one SQL Server and attaching it to a new server if the instance names are the same? Is this fairly straight forward? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-24 : 18:08:31
|
| Unless you detach the databases from the original instance beforehand, you risk corrupting them. You'd also have to attach them on the new instance, they won't just magically appear. |
 |
|
|
Kotetsu
Starting Member
3 Posts |
Posted - 2012-04-24 : 20:12:00
|
| I should have been more specific. I understand that databases that are detached in SQL must be re-attached. I was not sure however, if detaching them in SQL was absolutely necessary when basically mounting the SAN LUN where they already exist to a new SQL Server where the instance name is the same. Of course, all transactions must be committed, etc...I was not sure about:1. If it would work without completely trashing the database(s)2. How the new SQL Server would (magically) know about/be able to use the databases if you didn't detach and re-attach in SQLIt's all hypothetical anyway. I was just wondering if the scenario was a possible low down-time solution for migrating from one physical SQL box to another where the storage is not changing. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-24 : 20:36:06
|
| Once you've detached the database, the files are no longer open and can be moved freely. In your example, while you're not physically moving the files, you're logically moving them to another SQL Server instance. Once the LUN is presented to the new instance, the files can be attached just as they would on the original server, i.e., by specifying their path. It would be no different than detaching and copying the files to a LUN already linked to the new instance.Detaching will flush any dirty buffers to disk, and disconnect everyone in a transactionally consistent manner. Once that's done the files are free to move and attach to any other instance. If you don't detach, you could end up with files in an inconsistent state that SQL Sever cannot recover or reattach. To be safe, you should make full database and log backups before you detach and keep them handy in case something goes wrong. |
|
|
|
Kotetsu
Starting Member
3 Posts |
Posted - 2012-04-24 : 20:40:59
|
| Thanks. I am hoping to never do it in this manner, but the suggestion was made by someone else, and I honestly didn't know enough to rule it out or not. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-04-24 : 21:12:05
|
| Additionally, you will find that you MUST detach in order to present the LUN to a different instance.It sounds like you're talking about SAN Replication, in which case, more commonly, you're actually presenting snapshots of the Volume (not to be confused with LUN) to the new host. In this case the target host must detach the databases before the snapshot can update its' mirror partner(s). |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-04-24 : 21:13:58
|
| Also, In short, anything that can move, resize or really do anything to data files without 1st stopping SQL Server or detaching, is almost guranteed to corrupt the files or crash SQL Server. |
|
|
|
|
|
|
|
|
Detach LUN and re-attach to different server