Multiple data files - impact on log shipping/backups

Question

Multiple data files - impact on log shipping/backups

Indianrock

SSC-Insane

Points: 20333
More actions
June 5, 2008 at 6:26 am

#124133

SQL 2005 enterprise on active/passive cluster, single mdf is now at 205GB. If we go to one mdf with multiple ndf data files spread over different portions of a disk array ( SAN or Netapp device ), will this require reconfiguration of our regular native sql backups or log shipping? What if the log shipping standby server only uses local drives ( no SAN or netapp ), if for no other reason I'm assuming it would need the same multiple data file setup if it was to perform as the data grows into terrabytes over the next year.
What if we also implement table partitioning, same questions?

Viewing 7 posts - 1 through 7 (of 7 total)

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

shouldn't be a problem, the standby SQL server will control it's local data files depending on how you configure them.

not the same, but our replicated db's have a different file structure than the publishers and we don't have any problems

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

Yeah, I see no issues with that.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Indianrock SSC-Insane Points: 20333 More actions · Answer 3

In a test area I set up log shipping, then added a data file ( ndf ) to the primary file group. It appears this transaction was log shipped to the standby database and the new ndf file created there. I'm wondering what would have happened if the specified data folder didn't exist on the standby box.

USE [master]

GO

ALTER DATABASE [nmacLogShiptest] ADD FILE ( NAME = N'NMACData2', FILENAME = N'F:\MSSQL\Data\NMACData2.ndf' , SIZE = 20480000KB , FILEGROWTH = 10240KB ) TO FILEGROUP [PRIMARY]

GO

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

alen teplitsky

SSC-Dedicated

Points: 30014

June 6, 2008 at 8:27 am

#825486

the data would go into the mdf

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

I'll test this to see if the mdf grows as you indicated. I also need to test to see how log shipping handles partitioning of a table. Have you found specific BOL articles or other web sources detailing the impacts on log shipping of such steps?

Indianrock SSC-Insane Points: 20333 More actions · Answer 6

When you add a data file to the source database along a path which does not exist on the standby server, the transaction log won't restore. You have to manually restore it with the "MOVE" command to specify where the new ndf file should go.

RESTORE LOG [nmacLogShiptest] FROM

DISK = N'F:\mssql\logshipbackupsmacLogShiptest_20080606144330.trn' WITH

MOVE N'nmacdata3' TO N'f:\mssql\datamacData3.ndf', FILE = 1, STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_nmacLogShiptest.BAK', NOUNLOAD, STATS = 10

GO