[sims] SIMS/SQL and Backup Exec plan

[CadlaM]

I'd be very interested in SIMS current thinking on SQL backups. We had SIMS engineers set up SQL and SIMS. They left it 2 years a go running an MS schedule to kick of a SQL maintenance plan. We just updated our Symantec Backup Exec and I need to know the correct/best way to back up as BUE complains it cannot do incremental backup. this seems to be due to the SQL Maint Plan reseting archive state, but BUE not being able to do Inc backup of since last full from Itself.

[CadlaM]

I've asked the SIMS support desk the same question and they have not come back with anything yet. Only thing I could find was a 2010 document regarding dbattach manual backups.

 

Anyone any ideas?

[ass17]

Doesn't BUE have an SQL module (purchased separately) that allows backups of live DBs?

[CadlaM]

Yes BUE bought with SQL module. In fact it works if I do a full backup as SQL Management Plan also does a full 5 day a week bu to file on disk. BUE then complains when it wants to do an incremental b/u as it realises some sort of back up has been carried out since its last Full, so fails. Currently I am setting up a plan to do Weekly Full and Daily Incr BUE b/u Excluding SQL. SO that grab the SIMS files that they b/u via MP. Then at Weekend doing a Full BUE with no incrementals set of SQL only. I think that will give me belt and braces backup of SIMS requirement and a full system b/u.

 

FYI it seems recent BUE selling and licensing is same Agent cost for server, or Exchange, or SQL. So you just tell them you have, say, 8 agents including Exch and SQL.

[mukz]

how big is your database?

 

i do full hourly backups for our sims database.

It is 9gig, gets done in about 4 mins.

[vikpaw]

Never have multiple backup systems running like that, we had once ages ago and it breaks the chain of integrity. You're lucky it's complaining and not going ahead with the incremental.

 

If you did corrupt the chain like that, you'd not be able to use the incrementals only the Full ones.

 

Just stick to one system, and save it to disk or other location, then back up that location with the other system so they don't clash. I wouldn't have one method to do Full, and then another system to do Incrementals.

 

The main thing to think about is how do you want to restore and what are you willing to lose. I've never been convinced by differential / incremental on a database file. I'd rather have a full backup. We do a daily full backup. If you want more you can do them more frequently, or look at backing up transaction logs, but it's a bit overkill unless you know what you are doing and how to restore them. Plus some routines in SIMS will break the integrity, e.g. the shrinkfile routine to reduce your ldf file, the re-index patch also does something funny i think.

 

In terms of restore, if you use a full backup file or whatever you've got, then you will lose all data between that and the previous one. So we backup daily, and have to be prepared to lose a day's work. This is clearly unacceptable, i'd never ever want to restore to a backup, unless there was some major crazy issue.

 

It's more likely that you'll take your last good backup and restore to a test server and then extract manually or by report the bits of data you need and import them or manually enter them into the live system.

 

So the question is really - how likely is it that data created during the day, is subsequently deleted or corrupted before that night's backup. Usually not that likely.

 

Similar to regular file backups, if you do them nightly, then you're prepared to not handle files created and lost/deleted on the same day, unless you are also running shadow copies, versioning or undelete on the server.

 

We have an SQL plan to do nightly backups to disk, then the network guy points Backup Exec to the backup location and pulls them off using their grandfather differential strategy. As i'm doing full dailys it picks up each new file daily, and this goes to tape.

 

If you're confident with your BUE then use that and turn off the SQL stuff. I prefer to stick to doing it via SQL as it's built-in and i control it, i also have other maintenance tasks that i run around them, and i get emails when the task is successful etc. The main BUE stuff is across the system and a full backup at end of week can take over a day to do , so i let the NM worry about that side of it and whether it's successful in the long list of other backups it does.

 

The other thing with the backups is, make sure you do a test restore every now and then, to be sure whatever system you have is working and the files aren't corrupt.

[zag]

We just use Free SQL backup software for MS SQL Server 2008, 2012 and 2005 (Express) databases - SQLBackupFTP

 

Very cheap and does a perfect job of backing it up on a schedule.

 

the professional version also encrypts the data files which is very useful.

[Greenbeast]

We just use Free SQL backup software for MS SQL Server 2008, 2012 and 2005 (Express) databases - SQLBackupFTP

 

Very cheap and does a perfect job of backing it up on a schedule.

 

the professional version also encrypts the data files which is very useful.

 

just testing this before migrating to sql 2012 (which our current version of backup exec doesn't support)

The full backups (of the sims db) are only ~640Mb in size whereas the mdf file is 2.9Gb, do you know why this might be?

[zag]

just testing this before migrating to sql 2012 (which our current version of backup exec doesn't support)

The full backups (of the sims db) are only ~640Mb in size whereas the mdf file is 2.9Gb, do you know why this might be?

 

Yes it zips them. You can turn that setting off I believe.

 

The professional versions also do encryption so you could backup to a cloud drive directly if wanted.

[jinnantonnixx]

 

The other thing with the backups is, make sure you do a test restore every now and then, to be sure whatever system you have is working and the files aren't corrupt.

 

Great advice from @vikpaw. His last point is important - until you perform a test restore and check that your system works, all you have at the moment are 'alleged backups'.

 

As Vik also mentions, some SIMS tools will ruin your backups if you use incremental/differential backups, as the tools change your recovery model without asking permission or warning you.

Edited December 16, 2013 by jinnantonnixx

[Greenbeast]

Yes it zips them. You can turn that setting off I believe.

 

doh! I saw the files were zipped but figured it would never compress that much, just unzipped one and all is well

 

Great advice from @vikpaw. His last point is important - until you perform a test restore and check that your system works, all you have at the moment are 'alleged backups'.

 

With sql/sims what would be a good restore test? (i.e. without disrupting a live system)

[jinnantonnixx]

 

With sql/sims what would be a good restore test? (i.e. without disrupting a live system)

 

For DR, assume you have nothing to work with.

Ideally, on an isolated network, build a new server and restore the full system to that. You can do it all virtually, if you like, creating a virtual isolated network. Tune a SIMS client to the new server and check that it works. This way you will be able to prove that you can replace your SQL server with a slot-in replacement and still function with no tweaking (changing server names, instance names, connect.ini, Docstorage file structure, etc).

 

For a casual test (to simulate data loss, rather than server loss), you can restore a SIMS backup to a new database (e.g. TESTSIMS) using your existing SQL instance. Change the client's connect.ini to point to the new test database. Because you've effectively changed the name of the database, you'll need to do a few things to get it to work.

http://www.edugeek.net/forums/mis-systems/94948-cannot-login-after-sims-sql-restore.html << read this, it covers a lot of good stuff

 

Check you can log in, check it works. Now is the time to find problems, not when you're under pressure.

 

Oh yes, and make proper, clear instructions. The last thing you want when you're under pressure is to forget vital steps and make mistakes. And print the notes. On paper. Not left on a computer that won't start up.

Edited December 16, 2013 by jinnantonnixx

[Greenbeast]

Ok good plan, we're just going virtual bit by bit at the moment, i'm migrating to a new virtual sims machine, hence the need for a short term backup solution (until we buy into a more comprehensive/updated solution for all our servers)

So I will experiment with that in the new year

[zag]

If you use Veeam for backups they have a really good sandbox mode that you can use to test your backup strategies.

 

Although saying that, their VM backup is so good I've never needed it

 

The days of having to individually restore Sims data files or server OS's are long gone with Veeam! Thats the real power of snapshots and virtualization.

[vikpaw]

I've had a few issues with backups that are stored locally, especially with a VM, as it gets incorporated into the snapshots, and so total size can quickly grow.

Also it's not easy to clone / move a hulking great VM with snapshots. Admittedly I use them as a fallback to different versions, as it's much easier to restore a March backup to the 'just before you ran the spring update' version of sims server, than have to deal with the horror of working out how to downgrade the system.

I'd say if you create the backups to file, then redirect them to a fileserver / share. Not just a local drive.

[Greenbeast]

If you use Veeam for backups they have a really good sandbox mode that you can use to test your backup strategies.

 

Although saying that, their VM backup is so good I've never needed it

 

The days of having to individually restore Sims data files or server OS's are long gone with Veeam! Thats the real power of snapshots and virtualization.

 

Yes this is on the cards for next year, just need to get by for a few months

[2097]

We used to use MS SQL's built-in backup , but it basically made the file too large .

 

So heres an easy backup script i wrote .

 

Sims.net Complete Backup Script

 

Appends passwords , and Rars them up also .

 

We have them backed up onto our q nap which is attached to our Sims server as a drive/ISCSI .

 

If you dont have a Iscsi device and your using vmware , the easiest way would be to add a hard-disk as a raw drive to store the backups.

[vikpaw]

That's quite useful. Have you seen @matt40k 's Tidy Backups routine?

Some thoughts:

I'd be tempted to cut out the copying it all to a temp folder, especially the docstorage, and just redirect the output rar.

When you rar the backup, the source file is F:, which should be a C:

I'm not sure about creating and deleting the Temp folder. At least call it SIMSTemp just in case some other program wants to make a temp folder.

 

I use rar and you can save a profile that will auto delete, as well as fix the file time, do multiple files etc. Can you pass a rar'ing profile to the command line, that might save some lines too.

[matt40k]

Cheers @vikpaw, you can download it from CodePlex - TidyBackups - Home - it's free and open-source, you don't need to purchase a WinRar license (not free) and you also get the preserve option, with means you keep at least a set number of backups for each database.

[zag]

For 45 quid you can buy SQLBackupAndFTP which not only is a brilliant little sql server backup utility, but it can encrypt and upload to cloud storage Edited January 14, 2014 by zag

[matt40k]

I wouldn't recommend backing up your MIS to Google Drive, Dropbox, Skydrive, Amazon S3 - encrypted or not and definitely not to a FTP server! (must at least be SFTP) Also if it goes offsite you need to have the schools governers\SLT approval so they can sign off the risk, especially if it's going outside of the UK.