| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2008-04-09 : 04:30:32
|
| Hi,I know that some of you here work as DBAs in pretty large companies and I have a few questions about backup in such environments. As some of you might have picked up I recently got a job as a DBA in a major enterprise hosting company and we are having som backup/restore problems. We are currently using TSM (Tivoli) but it seems to be awfully slow and tedious compared to some of the tools out there, like LiteSpeed. We have in excess of 100 sql server installations at the moment...how do you guys do it in your environment?--Lumbago |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-09 : 12:32:10
|
| We use SQL Litespeed almost exclusively now for our production databases. For non-prod, we typically use the native backup command since the databases aren't that big in those environments. I believe we have about 300 SQL Servers. Did you have any specific questions?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-09 : 12:35:56
|
| We also have TSM (Which is the worst tool i have ever seen ).Check out SQLLitespeed,Redgate ."I believe we have about 300 SQL Servers."Tara, How do you manage 300 servers? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-09 : 13:24:40
|
| There's 4 SQL Server DBAs. We also have about 12 Oracle DBAs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-10 : 02:53:31
|
| Well, you allready answered what concerned me most, that LiteSpeed is working quite well in a large environment. I have never used it first hand myself but we used it in my previous job with great success. Here are a few questions that I'd like to ask someone else than Quest:- are the performance data on the Quest website accurate (up to 70% cut in backup/restore time spent and up to 95% compression on backup files) - how is it to work with in clustered/non-clustered setups? Is it all a walk in the park or do you have to do tweaking each time you need a restore?- does object-level restore actually work "the way I'm thinking it should", meaning that you can actually restore a single table or only a small set of tables? - how do you handle licensing?- is there anything I should know about when considering litespeed?As you understand I'll have to convince management (and a few of my colleagues actually) that this is a better option for us than what we do today, so I need a few bulletproof arguments. If we decide to start using it, we'll face a somewhat big investment so I would think management would say something like "we'd better make sure it's worth it" if you know what I mean.--Lumbago |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 13:13:03
|
| Yes the performance data looks accurate.In a clustered environment, the software needs to be installed on each node since the software is not cluster-aware.I've never used object level restore, but yes that's the idea.We license per CPU, so we've got a ton of licenses.I can't think of anything that needs to be done. It's an awesome product. There are other vendors that have similar products, so you might want to check them out too. I believe Red Gate has a product. It's worth the cost. We saved a ton of money in disks.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-10 : 13:23:10
|
| --In a clustered environment, the software needs to be installed on each node since the software is not cluster-aware(Is this for the LiteSpeed).Is the LiteSpeed actually backing up the MDF and LDF or just BAK.We just use good old SQL Backups - i generally kick of some scripts to take the back and restore to another environment for testing each day. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 14:05:18
|
| Yes I am referring to Litespeed. Litespeed backs up the database and not the files. Software that backs up the SQL Server database files is not sufficient for backups. At any rate, Litespeed uses extended stored procedures to do the work so it is inside SQL Server just like the native command.Good old SQL backups are not efficient for large databases nor do they save on space. It works great for small and medium databases but not large ones.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-04-10 : 14:41:01
|
| I have tested LiteSpeed object level and also record level restore. It works nicely, but only together with full backups. So you can not do an object level point in time restore.Ola Hallengrenhttp://ola.hallengren.com |
|
|
|
dcunningham
Starting Member
25 Posts |
Posted - 2008-04-10 : 18:44:20
|
| I've used Litespeed for almost a year now in a heavy online presence. for example one of our databases is 702 Gigs and with litespeed is compressed and encryped to 172 Gigs each night.Hope those stats help. Also for datacenter application. I've found the best way is to write a script that looks at a master control table and performs its backups calling the extended stored procedure to perform the backup for each database on all servers.Works like a charm. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-11 : 06:39:31
|
Wow, this is really excellent feedback! It could prove to be a time- AND moneysaver and management (not to mention the sales people) just have a tendency to love that! A few more question:- Have any of you done any benchmarking on SQL Backup Pro from Red-Gate in comparison with LiteSpeed? There is a test on the Red-gate website performed by someone called "The Tolly Group" (never heared of them) and Red-Gates product actually outperforms LitesSpeed at only "a fraction" of the cost. Can anyone verify this?- @dcunningham: can you please elaborate a bit on this -> quote:
"I've found the best way is to write a script that looks at a master control table and performs its backups calling the extended stored procedure to perform the backup for each database on all servers."
--Lumbago |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-11 : 11:10:39
|
| That is some hugh database 720 GIG how do you manage to do the nightly jobs and indexesI thought ours was big at 70 GIG but 720 thats hugh. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-11 : 11:13:35
|
| Have you thought of doing Table-Partitioning for that Huge database? We have done it for 1.2 TB database and performance is going like dream. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-04-11 : 12:11:04
|
| I was doing some testing recently with LiteSpeed, and found that I was getting about 70% compression on the lowest compression level with my test database.I found that the higher compression levels were nor that useful, because they dramatically increased the CPU usage and runtime for backups with not that much increase in compression. For example, at compression level 1, the backup ran in 191 seconds with 70% compression, at compression level 2 the run time was 292 seconds with 76% compression, and at compression level 11 the backup ran in 4000 seconds with 79% compression. Native backups of the same database ran in 625 seconds. One interesting result was the LiteSpeed backups ran in about 50% of the time for native backups even with no compression on the LiteSpeed backup.I recommend you do your own testing before implementing a solution.The object level restore is a nice feature, but is only available in the Enterprise Edition. I was given a price of $795 per server for Standard Edition and $2495 per server for Enterprise Edition, so you can decide it that is worth the price for that feature.CODO ERGO SUM |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-11 : 12:19:46
|
| I used both LightSpeed and SQLSafe (Idera). Both performed about the same. SQLSafe offered encrpytion when we got (the main reason we got it). It was also cheaper than LightSpeed, at least at the time. I found LightSpeed when it was still being sold exclusively from Australia (I believe it was by Imceda back then). I've never had an issue with either products or their support. Check out the eval copies of the products mentioned (and the prices). With that many servers, the vendors should be willing to deal.Terry |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-11 : 18:02:13
|
| Table Partitioning - thats another topic...perhaps create a new thread i be sure to come over to it.Just implementing SQL 2005 as i type with 100 db...then on to the new things like partitioning..Yes agree with own benchmarks at end of day depends if you have enough space and fast CPU to run the restores. |
|
|
|
|
Backup in large datacenters