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 |
|
hvdtol
Yak Posting Veteran
50 Posts |
 Posted - 2006-04-15 : 08:54:15
|
| In our organisation we manage databases with Sql Sever and Oracle.In Oracle we use tablespaces, but not larger then 4 Gb. When a tablespace becomes full, we extended it with an extra datafile.This way the backup files never become larger then 4Gb.Why do we do this?- we have had troubles in the past with very large datafiles; the backup program corrupted sometimes large datafile. Now it is our policy never to have datafiles grow over 4Gb.- Also we think smaller datafiles are handeled better.We want to adopt the same policy for Sql Server.Two applications are the reason for this: MOM(Microsoft Operations Manager) and Sharepoint; both application can grow to very large databases, and very long backup times.We want to do the following:- Create datafiles for each database with a max of 4Gb per datafile- Add more datafiles when needed to the same filegroup- We will not define specific tables into a specific filegroup- We will use one filegroup; perhaps two to sperate the system tables- We want to make datafile backups and follow the same strategy as with Oracle: never make physical files grow over 4Gb.Can anybody give me any advice.Is this a workable situation or are will we face problems we might not want? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-15 : 09:25:30
|
| Did you ever address why your backup program was corrupting files?I would have thought the obvious solution was to address the way the backups were taken rather than change your installation to fit in with the backups.If it corrupts large files I wouldn't trust it with 4G ones even if it appears to work.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-15 : 09:59:38
|
| You have made the assumption that because you had a problem with Oracle, that you will have a similar problem with SQL Server. I have never seem this problem with SQL Server, and I manage at least a hundred databases with datafiles much larger that 4 GB.If your database is large, it will take longer to backup, whether you do a full database backup, or backup individual files or filegroups. Almost everyone does full database backups with SQL Server, unless the database is so large that individual file or filegroup backups are more managable.I advise you to be wary of taking a plan that works with one RDBMS, and implementing it with another.You should really should read the sections on Backup and Recovery in SQL Server Books Online before you do this.CODO ERGO SUM |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-15 : 10:03:41
|
| >> I have never seem this problem with SQL ServerI have but it's because people are backing up direct to tape (a tape driver problem) or trying to copy the data files (a user problem) rather than doing a backup.Neither is a sql server problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2006-04-15 : 10:18:39
|
| Thanks for the replies.No we have never found the reason for corruption, we only noticed this occasionally happened with a large file (>4Gb).At which moment comes the point that individual files are more manageble to backup? Is it 10Gb, 100Gb or 1Tb? And what could be the reason then, if we do not separate historical data into filegroups?We are worried to use large files.Many small files sounds more managable then one large file of for example 1Tb.Does nobody have had any problem using large files?Harry |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-15 : 10:24:19
|
| I would probably split things up a bit for 1T but have regularly backed up a couple of hundred gig without problem.Making smaller files is a bit more efficient but for backups and restores the fewrwe files makes things simpler.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Backup with datafile question