Francisco Tapia
fhtapia at gmail.com
Wed Oct 6 13:18:46 CDT 2010
In our environment I have a job schedule to do the backup and restore solution. Off the top of my head I'm not sure why you would want to bother with the detach / attach situation. The backup / restore solution works very well for me, We use red-gate's sql backup which compresses our 1.7 terabyte database down to 282gb, Then we restore the database to our Testing servers. Time is a difficult thing here because for a full backup over a 1gb network, this can take close to 14hrs, So to help minimize data traffic we take a monthly FULL backup with weekly differential backups. This speeds up the entire process by a lot by simply reducing total backup time down to 12 minutes on the restore side, it can take up to 20 hrs to restore the entire contents simply because of the problem with the slow disks. The purpose for doing it this way is that the production database can remain online and continue to get used while the system backs up. We can't afford to have the production system offline for too long and thus we think this is the best option at the moment.. We recently have begun to test filegroup backups. This enables us to take the filegroup backups and restore them onto the testing system and allows you at the testing system to begin logging in and using the new data as soon as possible while the system is still restoring!... very wild... but a few more things need to fall in place before we roll out this solution. Time out your Backup/Restore vs the Copy of files... that should yield what is best for your situation. Everybody here has different hardware and it'd be kinda cool to hear how others have solved their data moving projects. -Francisco http://bit.ly/sqlthis | Tsql and More... On Wed, Oct 6, 2010 at 10:50 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > I am trying to use commercial solid state disks > > http://www.newegg.com/Product/Product.aspx?Item=N82E16820227551 > > in Raid 0 to hold my database from hell as well as another database that > contains related > information. My theory is that under normal use these two databases are > read only. As such I can > do reads from them all day without worrying about hot spot wear on the > SSDs. > > In theory placing these databases on SSD should provide an enormous > performance boost vs rotating > media since SSD streaming read values are much higher and the IOPS is > enormously higher. My problem > is that I know of no way to discover what the write pattern looks like on > the disk as SQL Server > does (for example) an index build on adjacent fields (let's say 6 adjacent > fields) across 50 million > records on a table with an existing Clustered Index. Given that MLC SSDs > can only handle 10K writes > before potentially wearing out the cells, it seems risky to try and do this > kind of operations in > place on the SSDs. > > The problem is that I do need to maintain and build new indexes on these > databases. I also need to > keep a modern backup so that if one of the SSDs die (killing the entire > Raid 0 array) I can easily > get them back from backup. > > I am able to do backups easily, however I am struggling with how to easily > do maintenance work on > the nearline copy of the database and then get that copied to the online > location. > > Basically ATM I have two copies of the database in SQL Server - _DataHSID > and _DataNearLine. > "NearLine" is just part of the name to remind me that I need to do things > like updates and index > manipulations in that copy. > > _DataHSID is physically located on drive H: (the SSD array) whereas its log > file is in > F:\SQLServerLogFiles (rotating media) which is the default location for SQL > Server to build log files. > > _DataHSIDNearLine is physically located on rotating media J:\ and its log > file resides there too. > > So what I am trying to figure out is the easiest way to copy the NearLine > version to the OnLine > location. Since both are attached, it seems that I have to detach OnLine, > maybe copy (or detach and > move) NearLine to OnLine and reattach OnLine. > > I can use SMO to detach / attach objects programmatically, and C# can do > the copy for me, so I can > do all this using C# / .Net if that makes sense, and it appears to make > sense (to me). > > I figure I need to > 1) Detach both of the databases. > 2) Delete both of the log files, OnLine and nearLine > 3) Copy the NearLine database file to the SSD > 4) Attach both OnLine and nearLine. > > > BTW, HSID is currently about 50.5 million records, 560 fields, has a > clustered index and a handful > of multi-field non-clustered indexes on it. The file size for _DataHSID is > about 47 gigs. It is > set to grow by 500 megs at a shot, and has a mere 125 meg available free > space after all of the > operations done on it recently. It took 6 minutes and 30 seconds to > manually copy from rotating to SSD. > > Another possibility would be to do a backup / restore directly to the SSD. > The backup compresses > down to about 7.7 gigs, and a backup / restore would no doubt be faster. > > I just thought I'd check with others out here to see if anyone is doing > this kind of thing and has > solved the problem. > > TIA, > > -- > John W. Colby > www.ColbyConsulting.com > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >