|
|
You're in the
sql server replication
group:Moving Distribution mdf/ldf (maintain logical drive/path) ?
sql server replication:
breaking transactional replication under SQL 2000 ? I need to do this so I can get both distribution mdf/ldf files off single disk volumes (logical drives), and onto RAID arrays for fault tolerance. Current Setup: Dist Data File PATH = D:\distribution.mdf (single physical disk drive) Dist Log File PATH = E:\distribution.ldf (single physical disk drive) Can I... 1 - Prevent any user access to published DB, wait for "No replicated transaction available" message between Publisher/Subscribers 2- Shut down Log Reader Agent and Distributor Agents 3 - Shut down SQL Server on Distributor (same server as Publisher) 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp location 5 - Delete Logical Drives D: and E: in O/S 6 - Create new multi disk arrays in RAID management and assign new logical drives D: and E:, format, etc.. 7 - Copy (again at the O/S level) the distribution mdf/ldf files back to the new logical drives D: and E: now on the RAID arrays 8 - Start up SQL Server on the Distributor 9 - Resume the Log Reader and Dist Agents Is my assumption accurate in that so long as I DO NOT in any way alter the logical drive letter and path to the distribution mdf/ldf files when SQL is started back up, there should be no issues ? I don't think I want to detach/attach the distribution DB, (while SQL is running) nor use backup/restore either. Is this possible ? If not, is there any other workarounds other than to delete and recreate the replication setup all over again from scratch and recreate the Distributor Properties ? Thanks in advance. -Frank
You can do this, but it is not recommended you copy open files in this way.
Some points. You do not have to shut down the log reader or distribution agents. They will pick up where they left off when they restart. The problem is that when you shut down SQL Servers, the databases are not always in a state where you can copy them somewhere else and then re-attach them. The correct way to do this is to detach and then reattach the distribution database which cannot be done. So if you do pursue this, I would do a backup of the distribution database before the move. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... > Is it possible to move the Distribution mdf/ldf files without completely > breaking transactional replication under SQL 2000 ? > > I need to do this so I can get both distribution mdf/ldf files off single > disk volumes (logical drives), and onto RAID arrays for fault tolerance. > > Current Setup: > > Dist Data File PATH = D:\distribution.mdf > (single physical disk drive) > > Dist Log File PATH = E:\distribution.ldf > (single physical disk drive) > > Can I... > > 1 - Prevent any user access to published DB, wait for "No replicated > transaction available" message between Publisher/Subscribers > 2- Shut down Log Reader Agent and Distributor Agents > 3 - Shut down SQL Server on Distributor (same server as Publisher) > 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp > location > 5 - Delete Logical Drives D: and E: in O/S > 6 - Create new multi disk arrays in RAID management and assign new logical > drives D: and E:, format, etc.. > 7 - Copy (again at the O/S level) the distribution mdf/ldf files back to > the new logical drives D: and E: now on the RAID arrays > 8 - Start up SQL Server on the Distributor > 9 - Resume the Log Reader and Dist Agents > > Is my assumption accurate in that so long as I DO NOT in any way alter the > logical drive letter and path to the distribution mdf/ldf files when SQL > is started back up, there should be no issues ? I don't think I want to > detach/attach the distribution DB, (while SQL is running) nor use > backup/restore either. > > Is this possible ? If not, is there any other workarounds other than to > delete and recreate the replication setup all over again from scratch and > recreate the Distributor Properties ? > > Thanks in advance. > > -Frank >
Hi Hilary,
Thanks for the quick reply. I think you may have missed my intent to *not* use detach/attach of the distribution database ? I will shut down SQL Service at the Publisher/Distributor (same server), then filecopy at the O/S level the distribution mdf/ldf files to a temp location, then copy them back to the same logical drive letter and path once I create them under a new raid array config. If SQL is not running when I O/S filecopy the distribution mdf/ldf files around that should be ok right as they won't be *open* files in use by SQL server ? They will both end up back at the same exact logical drive letter and path they were originally *before* I start SQL service back up again. My thinking is that as far as SQL knows, nothing is changing at all....it's like I did a reboot of the server and the only difference is that the logical D: and E: drives that used to be single physical disks respectively are now logical drives on top of a hardware RAID array so they are fault tolerant against any single drive failure. I hope this makes sense ? Thanks, Frank [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:%232QYDTR0HHA.3400@TK2MSFTNGP03.phx.gbl... > You can do this, but it is not recommended you copy open files in this > way. > > Some points. You do not have to shut down the log reader or distribution > agents. They will pick up where they left off when they restart. The > problem is that when you shut down SQL Servers, the databases are not > always in a state where you can copy them somewhere else and then > re-attach them. The correct way to do this is to detach and then reattach > the distribution database which cannot be done. > > So if you do pursue this, I would do a backup of the distribution database > before the move. > > > -- > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message > news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... >> Is it possible to move the Distribution mdf/ldf files without completely >> breaking transactional replication under SQL 2000 ? >> >> I need to do this so I can get both distribution mdf/ldf files off single >> disk volumes (logical drives), and onto RAID arrays for fault tolerance. >> >> Current Setup: >> >> Dist Data File PATH = D:\distribution.mdf >> (single physical disk drive) >> >> Dist Log File PATH = E:\distribution.ldf >> (single physical disk drive) >> >> Can I... >> >> 1 - Prevent any user access to published DB, wait for "No replicated >> transaction available" message between Publisher/Subscribers >> 2- Shut down Log Reader Agent and Distributor Agents >> 3 - Shut down SQL Server on Distributor (same server as Publisher) >> 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp >> location >> 5 - Delete Logical Drives D: and E: in O/S >> 6 - Create new multi disk arrays in RAID management and assign new >> logical drives D: and E:, format, etc.. >> 7 - Copy (again at the O/S level) the distribution mdf/ldf files back to >> the new logical drives D: and E: now on the RAID arrays >> 8 - Start up SQL Server on the Distributor >> 9 - Resume the Log Reader and Dist Agents >> >> Is my assumption accurate in that so long as I DO NOT in any way alter >> the logical drive letter and path to the distribution mdf/ldf files when >> SQL is started back up, there should be no issues ? I don't think I want >> to detach/attach the distribution DB, (while SQL is running) nor use >> backup/restore either. >> >> Is this possible ? If not, is there any other workarounds other than to >> delete and recreate the replication setup all over again from scratch and >> recreate the Distributor Properties ? >> >> Thanks in advance. >> >> -Frank >> > >
No, I understand perfectly what you are tying to do, and it is probably the
way I would be forced to do it. I am trying to point out to you the gotcha that database files are sometimes not always closed correctly on server shutdown. Microsoft recommends if you are going to move database files you detach them first. This is something you can't do with the distribution database or published databases. This is why I am urging you to have current backups. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message news:uDrg0qR0HHA.3940@TK2MSFTNGP05.phx.gbl... > Hi Hilary, > > Thanks for the quick reply. I think you may have missed my intent to *not* > use detach/attach of the distribution database ? > > I will shut down SQL Service at the Publisher/Distributor (same server), > then filecopy at the O/S level the distribution mdf/ldf files to a temp > location, then copy them back to the same logical drive letter and path > once I create them under a new raid array config. > > If SQL is not running when I O/S filecopy the distribution mdf/ldf files > around that should be ok right as they won't be *open* files in use by SQL > server ? > > They will both end up back at the same exact logical drive letter and path > they were originally *before* I start SQL service back up again. > > My thinking is that as far as SQL knows, nothing is changing at > all....it's like I did a reboot of the server and the only difference is > that the logical D: and E: drives that used to be single physical disks > respectively are now logical drives on top of a hardware RAID array so > they are fault tolerant against any single drive failure. > > I hope this makes sense ? > > Thanks, > > Frank > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%232QYDTR0HHA.3400@TK2MSFTNGP03.phx.gbl... >> You can do this, but it is not recommended you copy open files in this >> way. >> >> Some points. You do not have to shut down the log reader or distribution >> agents. They will pick up where they left off when they restart. The >> problem is that when you shut down SQL Servers, the databases are not >> always in a state where you can copy them somewhere else and then >> re-attach them. The correct way to do this is to detach and then reattach >> the distribution database which cannot be done. >> >> So if you do pursue this, I would do a backup of the distribution >> database before the move. >> >> >> -- >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >> news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... >>> Is it possible to move the Distribution mdf/ldf files without completely >>> breaking transactional replication under SQL 2000 ? >>> >>> I need to do this so I can get both distribution mdf/ldf files off >>> single disk volumes (logical drives), and onto RAID arrays for fault >>> tolerance. >>> >>> Current Setup: >>> >>> Dist Data File PATH = D:\distribution.mdf >>> (single physical disk drive) >>> >>> Dist Log File PATH = E:\distribution.ldf >>> (single physical disk drive) >>> >>> Can I... >>> >>> 1 - Prevent any user access to published DB, wait for "No replicated >>> transaction available" message between Publisher/Subscribers >>> 2- Shut down Log Reader Agent and Distributor Agents >>> 3 - Shut down SQL Server on Distributor (same server as Publisher) >>> 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp >>> location >>> 5 - Delete Logical Drives D: and E: in O/S >>> 6 - Create new multi disk arrays in RAID management and assign new >>> logical drives D: and E:, format, etc.. >>> 7 - Copy (again at the O/S level) the distribution mdf/ldf files back to >>> the new logical drives D: and E: now on the RAID arrays >>> 8 - Start up SQL Server on the Distributor >>> 9 - Resume the Log Reader and Dist Agents >>> >>> Is my assumption accurate in that so long as I DO NOT in any way alter >>> the logical drive letter and path to the distribution mdf/ldf files when >>> SQL is started back up, there should be no issues ? I don't think I want >>> to detach/attach the distribution DB, (while SQL is running) nor use >>> backup/restore either. >>> >>> Is this possible ? If not, is there any other workarounds other than to >>> delete and recreate the replication setup all over again from scratch >>> and recreate the Distributor Properties ? >>> >>> Thanks in advance. >>> >>> -Frank >>> >> >> >
Hi Hilary,
Thanks, I defnitely appreciate the advise and will definitely make a backup of the Distrbution DB before hand. I guess where I don't follow entirely is how SQL Server would have any knowledge that the mdf/ldf files were ever moved if when it starts back up it finds them in te exact same drive letter and path they were when it was shutdown ? Otherwise the risk you noted would seem to apply *anytime* SQL is shutdown and restarted I would imagine ? I think I'll also make sure that before I shut down SQL that no processes are accessing the Distribution DB, which if I manually stop the Dist Agents once "no replicated transactions are available" appears in EM Repl Monitor than that would be an additional pre-cautionary step. In any event, I'm going to be doing this tomorrow on our PROD systems, so I'll be sure and report back any success/failure of the process. Thanks again ! -Frank [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:%234p9gLT0HHA.2484@TK2MSFTNGP06.phx.gbl... > No, I understand perfectly what you are tying to do, and it is probably > the way I would be forced to do it. > > I am trying to point out to you the gotcha that database files are > sometimes not always closed correctly on server shutdown. Microsoft > recommends if you are going to move database files you detach them first. > This is something you can't do with the distribution database or published > databases. > > This is why I am urging you to have current backups. > > -- > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message > news:uDrg0qR0HHA.3940@TK2MSFTNGP05.phx.gbl... >> Hi Hilary, >> >> Thanks for the quick reply. I think you may have missed my intent to >> *not* use detach/attach of the distribution database ? >> >> I will shut down SQL Service at the Publisher/Distributor (same server), >> then filecopy at the O/S level the distribution mdf/ldf files to a temp >> location, then copy them back to the same logical drive letter and path >> once I create them under a new raid array config. >> >> If SQL is not running when I O/S filecopy the distribution mdf/ldf files >> around that should be ok right as they won't be *open* files in use by >> SQL server ? >> >> They will both end up back at the same exact logical drive letter and >> path they were originally *before* I start SQL service back up again. >> >> My thinking is that as far as SQL knows, nothing is changing at >> all....it's like I did a reboot of the server and the only difference is >> that the logical D: and E: drives that used to be single physical disks >> respectively are now logical drives on top of a hardware RAID array so >> they are fault tolerant against any single drive failure. >> >> I hope this makes sense ? >> >> Thanks, >> >> Frank >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:%232QYDTR0HHA.3400@TK2MSFTNGP03.phx.gbl... >>> You can do this, but it is not recommended you copy open files in this >>> way. >>> >>> Some points. You do not have to shut down the log reader or distribution >>> agents. They will pick up where they left off when they restart. The >>> problem is that when you shut down SQL Servers, the databases are not >>> always in a state where you can copy them somewhere else and then >>> re-attach them. The correct way to do this is to detach and then >>> reattach the distribution database which cannot be done. >>> >>> So if you do pursue this, I would do a backup of the distribution >>> database before the move. >>> >>> >>> -- >>> Looking for a SQL Server replication book? >>> http://www.nwsu.com/0974973602.html >>> >>> Looking for a FAQ on Indexing Services/SQL FTS >>> http://www.indexserverfaq.com >>> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >>> news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... >>>> Is it possible to move the Distribution mdf/ldf files without >>>> completely breaking transactional replication under SQL 2000 ? >>>> >>>> I need to do this so I can get both distribution mdf/ldf files off >>>> single disk volumes (logical drives), and onto RAID arrays for fault >>>> tolerance. >>>> >>>> Current Setup: >>>> >>>> Dist Data File PATH = D:\distribution.mdf >>>> (single physical disk drive) >>>> >>>> Dist Log File PATH = E:\distribution.ldf >>>> (single physical disk drive) >>>> >>>> Can I... >>>> >>>> 1 - Prevent any user access to published DB, wait for "No replicated >>>> transaction available" message between Publisher/Subscribers >>>> 2- Shut down Log Reader Agent and Distributor Agents >>>> 3 - Shut down SQL Server on Distributor (same server as Publisher) >>>> 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp >>>> location >>>> 5 - Delete Logical Drives D: and E: in O/S >>>> 6 - Create new multi disk arrays in RAID management and assign new >>>> logical drives D: and E:, format, etc.. >>>> 7 - Copy (again at the O/S level) the distribution mdf/ldf files back >>>> to the new logical drives D: and E: now on the RAID arrays >>>> 8 - Start up SQL Server on the Distributor >>>> 9 - Resume the Log Reader and Dist Agents >>>> >>>> Is my assumption accurate in that so long as I DO NOT in any way alter >>>> the logical drive letter and path to the distribution mdf/ldf files >>>> when SQL is started back up, there should be no issues ? I don't think >>>> I want to detach/attach the distribution DB, (while SQL is running) nor >>>> use backup/restore either. >>>> >>>> Is this possible ? If not, is there any other workarounds other than >>>> to delete and recreate the replication setup all over again from >>>> scratch and recreate the Distributor Properties ? >>>> >>>> Thanks in advance. >>>> >>>> -Frank >>>> >>> >>> >> > >
The caveat has nothing to do with the move itself. Rather the fact that
stopping SQL Server does not leave the databases in a consistent state. This is the problem with your approach. I don't think you have an alternative. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message news:OkmZt3U0HHA.4476@TK2MSFTNGP06.phx.gbl... > Hi Hilary, > > Thanks, I defnitely appreciate the advise and will definitely make a > backup of the Distrbution DB before hand. > > I guess where I don't follow entirely is how SQL Server would have any > knowledge that the mdf/ldf files were ever moved if when it starts back up > it finds them in te exact same drive letter and path they were when it was > shutdown ? > > Otherwise the risk you noted would seem to apply *anytime* SQL is shutdown > and restarted I would imagine ? > > I think I'll also make sure that before I shut down SQL that no processes > are accessing the Distribution DB, which if I manually stop the Dist > Agents once "no replicated transactions are available" appears in EM Repl > Monitor than that would be an additional pre-cautionary step. > > In any event, I'm going to be doing this tomorrow on our PROD systems, so > I'll be sure and report back any success/failure of the process. > > Thanks again ! > > -Frank > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%234p9gLT0HHA.2484@TK2MSFTNGP06.phx.gbl... >> No, I understand perfectly what you are tying to do, and it is probably >> the way I would be forced to do it. >> >> I am trying to point out to you the gotcha that database files are >> sometimes not always closed correctly on server shutdown. Microsoft >> recommends if you are going to move database files you detach them first. >> This is something you can't do with the distribution database or >> published databases. >> >> This is why I am urging you to have current backups. >> >> -- >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >> news:uDrg0qR0HHA.3940@TK2MSFTNGP05.phx.gbl... >>> Hi Hilary, >>> >>> Thanks for the quick reply. I think you may have missed my intent to >>> *not* use detach/attach of the distribution database ? >>> >>> I will shut down SQL Service at the Publisher/Distributor (same server), >>> then filecopy at the O/S level the distribution mdf/ldf files to a temp >>> location, then copy them back to the same logical drive letter and path >>> once I create them under a new raid array config. >>> >>> If SQL is not running when I O/S filecopy the distribution mdf/ldf files >>> around that should be ok right as they won't be *open* files in use by >>> SQL server ? >>> >>> They will both end up back at the same exact logical drive letter and >>> path they were originally *before* I start SQL service back up again. >>> >>> My thinking is that as far as SQL knows, nothing is changing at >>> all....it's like I did a reboot of the server and the only difference is >>> that the logical D: and E: drives that used to be single physical disks >>> respectively are now logical drives on top of a hardware RAID array so >>> they are fault tolerant against any single drive failure. >>> >>> I hope this makes sense ? >>> >>> Thanks, >>> >>> Frank >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:%232QYDTR0HHA.3400@TK2MSFTNGP03.phx.gbl... >>>> You can do this, but it is not recommended you copy open files in this >>>> way. >>>> >>>> Some points. You do not have to shut down the log reader or >>>> distribution agents. They will pick up where they left off when they >>>> restart. The problem is that when you shut down SQL Servers, the >>>> databases are not always in a state where you can copy them somewhere >>>> else and then re-attach them. The correct way to do this is to detach >>>> and then reattach the distribution database which cannot be done. >>>> >>>> So if you do pursue this, I would do a backup of the distribution >>>> database before the move. >>>> >>>> >>>> -- >>>> Looking for a SQL Server replication book? >>>> http://www.nwsu.com/0974973602.html >>>> >>>> Looking for a FAQ on Indexing Services/SQL FTS >>>> http://www.indexserverfaq.com >>>> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >>>> news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... >>>>> Is it possible to move the Distribution mdf/ldf files without >>>>> completely breaking transactional replication under SQL 2000 ? >>>>> >>>>> I need to do this so I can get both distribution mdf/ldf files off >>>>> single disk volumes (logical drives), and onto RAID arrays for fault >>>>> tolerance. >>>>> >>>>> Current Setup: >>>>> >>>>> Dist Data File PATH = D:\distribution.mdf >>>>> (single physical disk drive) >>>>> >>>>> Dist Log File PATH = E:\distribution.ldf >>>>> (single physical disk drive) >>>>> >>>>> Can I... >>>>> >>>>> 1 - Prevent any user access to published DB, wait for "No replicated >>>>> transaction available" message between Publisher/Subscribers >>>>> 2- Shut down Log Reader Agent and Distributor Agents >>>>> 3 - Shut down SQL Server on Distributor (same server as Publisher) >>>>> 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp >>>>> location >>>>> 5 - Delete Logical Drives D: and E: in O/S >>>>> 6 - Create new multi disk arrays in RAID management and assign new >>>>> logical drives D: and E:, format, etc.. >>>>> 7 - Copy (again at the O/S level) the distribution mdf/ldf files back >>>>> to the new logical drives D: and E: now on the RAID arrays >>>>> 8 - Start up SQL Server on the Distributor >>>>> 9 - Resume the Log Reader and Dist Agents >>>>> >>>>> Is my assumption accurate in that so long as I DO NOT in any way alter >>>>> the logical drive letter and path to the distribution mdf/ldf files >>>>> when SQL is started back up, there should be no issues ? I don't think >>>>> I want to detach/attach the distribution DB, (while SQL is running) >>>>> nor use backup/restore either. >>>>> >>>>> Is this possible ? If not, is there any other workarounds other than >>>>> to delete and recreate the replication setup all over again from >>>>> scratch and recreate the Distributor Properties ? >>>>> >>>>> Thanks in advance. >>>>> >>>>> -Frank >>>>> >>>> >>>> >>> >> >> >
Hi Hilary,
I'm happy to report no issues whatsoever with the process, everything worked just fine. Thanks again for your advice and insight ! Regards, Frank [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:O$aNPUW0HHA.4928@TK2MSFTNGP03.phx.gbl... > The caveat has nothing to do with the move itself. Rather the fact that > stopping SQL Server does not leave the databases in a consistent state. > This is the problem with your approach. I don't think you have an > alternative. > > -- > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message > news:OkmZt3U0HHA.4476@TK2MSFTNGP06.phx.gbl... >> Hi Hilary, >> >> Thanks, I defnitely appreciate the advise and will definitely make a >> backup of the Distrbution DB before hand. >> >> I guess where I don't follow entirely is how SQL Server would have any >> knowledge that the mdf/ldf files were ever moved if when it starts back >> up it finds them in te exact same drive letter and path they were when it >> was shutdown ? >> >> Otherwise the risk you noted would seem to apply *anytime* SQL is >> shutdown and restarted I would imagine ? >> >> I think I'll also make sure that before I shut down SQL that no processes >> are accessing the Distribution DB, which if I manually stop the Dist >> Agents once "no replicated transactions are available" appears in EM Repl >> Monitor than that would be an additional pre-cautionary step. >> >> In any event, I'm going to be doing this tomorrow on our PROD systems, so >> I'll be sure and report back any success/failure of the process. >> >> Thanks again ! >> >> -Frank >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:%234p9gLT0HHA.2484@TK2MSFTNGP06.phx.gbl... >>> No, I understand perfectly what you are tying to do, and it is probably >>> the way I would be forced to do it. >>> >>> I am trying to point out to you the gotcha that database files are >>> sometimes not always closed correctly on server shutdown. Microsoft >>> recommends if you are going to move database files you detach them >>> first. This is something you can't do with the distribution database or >>> published databases. >>> >>> This is why I am urging you to have current backups. >>> >>> -- >>> Looking for a SQL Server replication book? >>> http://www.nwsu.com/0974973602.html >>> >>> Looking for a FAQ on Indexing Services/SQL FTS >>> http://www.indexserverfaq.com >>> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >>> news:uDrg0qR0HHA.3940@TK2MSFTNGP05.phx.gbl... >>>> Hi Hilary, >>>> >>>> Thanks for the quick reply. I think you may have missed my intent to >>>> *not* use detach/attach of the distribution database ? >>>> >>>> I will shut down SQL Service at the Publisher/Distributor (same >>>> server), then filecopy at the O/S level the distribution mdf/ldf files >>>> to a temp location, then copy them back to the same logical drive >>>> letter and path once I create them under a new raid array config. >>>> >>>> If SQL is not running when I O/S filecopy the distribution mdf/ldf >>>> files around that should be ok right as they won't be *open* files in >>>> use by SQL server ? >>>> >>>> They will both end up back at the same exact logical drive letter and >>>> path they were originally *before* I start SQL service back up again. >>>> >>>> My thinking is that as far as SQL knows, nothing is changing at >>>> all....it's like I did a reboot of the server and the only difference >>>> is that the logical D: and E: drives that used to be single physical >>>> disks respectively are now logical drives on top of a hardware RAID >>>> array so they are fault tolerant against any single drive failure. >>>> >>>> I hope this makes sense ? >>>> >>>> Thanks, >>>> >>>> Frank >>>> >>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>> news:%232QYDTR0HHA.3400@TK2MSFTNGP03.phx.gbl... >>>>> You can do this, but it is not recommended you copy open files in this >>>>> way. >>>>> >>>>> Some points. You do not have to shut down the log reader or >>>>> distribution agents. They will pick up where they left off when they >>>>> restart. The problem is that when you shut down SQL Servers, the >>>>> databases are not always in a state where you can copy them somewhere >>>>> else and then re-attach them. The correct way to do this is to detach >>>>> and then reattach the distribution database which cannot be done. >>>>> >>>>> So if you do pursue this, I would do a backup of the distribution >>>>> database before the move. >>>>> >>>>> >>>>> -- >>>>> Looking for a SQL Server replication book? >>>>> http://www.nwsu.com/0974973602.html >>>>> >>>>> Looking for a FAQ on Indexing Services/SQL FTS >>>>> http://www.indexserverfaq.com >>>>> "Frank Conte" <Frank.Conte@discussions.ms.com> wrote in message >>>>> news:uAGWG4L0HHA.1164@TK2MSFTNGP02.phx.gbl... >>>>>> Is it possible to move the Distribution mdf/ldf files without >>>>>> completely breaking transactional replication under SQL 2000 ? >>>>>> >>>>>> I need to do this so I can get both distribution mdf/ldf files off >>>>>> single disk volumes (logical drives), and onto RAID arrays for fault >>>>>> tolerance. >>>>>> >>>>>> Current Setup: >>>>>> >>>>>> Dist Data File PATH = D:\distribution.mdf >>>>>> (single physical disk drive) >>>>>> >>>>>> Dist Log File PATH = E:\distribution.ldf >>>>>> (single physical disk drive) >>>>>> >>>>>> Can I... >>>>>> >>>>>> 1 - Prevent any user access to published DB, wait for "No replicated >>>>>> transaction available" message between Publisher/Subscribers >>>>>> 2- Shut down Log Reader Agent and Distributor Agents >>>>>> 3 - Shut down SQL Server on Distributor (same server as Publisher) >>>>>> 4 - Copy the distribution mdf/ldf files (at the O/S level) to a temp >>>>>> location >>>>>> 5 - Delete Logical Drives D: and E: in O/S >>>>>> 6 - Create new multi disk arrays in RAID management and assign new >>>>>> logical drives D: and E:, format, etc.. >>>>>> 7 - Copy (again at the O/S level) the distribution mdf/ldf files back >>>>>> to the new logical drives D: and E: now on the RAID arrays >>>>>> 8 - Start up SQL Server on the Distributor >>>>>> 9 - Resume the Log Reader and Dist Agents >>>>>> >>>>>> Is my assumption accurate in that so long as I DO NOT in any way >>>>>> alter the logical drive letter and path to the distribution mdf/ldf >>>>>> files when SQL is started back up, there should be no issues ? I >>>>>> don't think I want to detach/attach the distribution DB, (while SQL >>>>>> is running) nor use backup/restore either. >>>>>> >>>>>> Is this possible ? If not, is there any other workarounds other than
Hello,
I have similar problem, my distribution database and log is configured at C drive, I have to move this database and log to another drive D. May anybody tell me how do this secure, please. This is production environment so I can't make any mistakes. SQL Server 2005 -- Regards
I've successfully moved distribution database at my server (SQL
2005).I used ALTER DATABASE more here: http://groups.google.pl/group/microsoft.public.sqlserver.server/browse_thread/thread/89b28d54a808c91e/6e1bee55a73098f2 -- Regards
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |