01/16/2004 Mssql2

[newmem]

if you have a programmer and that too a good professional programmer, while some may even buy ready-made scripts which use MS SQL as the db.

But still, we do need a sure way of being able to take our own backups, download them and possibly even restore them ourselves if needed. If it takes a couple of days to work on such an internal script then I would think that it would be worth it. We can atleast give the control and responsibility of taking the important backups to the end users.

I wouldn't expect any other hosting co. to even listen to such a request, but if I know Vortech then I think they are already thinking of some way to do this after the MSSQL2 scare.....hope my suggestion helps in developing an end-user-backup-system.

[Brangwyn]

Actually I don't think we do need another way to be able to take our own backups, what we need and to me this may still be the simplest thing, is just for Vortech to look at their current procedures and just make sure that they can Guarantee us a certain level of backup will be available. Then only those with super mission critical stuff may want or need to backup the data themselves as well (though these sort of things probably shouldn't be on a shared server to begin with, they should be somewhere where they can use full logging and incremental + periodic full backup cycles).

Difference of opinions, but we're both wanting the same thing but in different ways I guess.

[antic]

So just out of curiosity, what did anyone think of my suggestion? I feel a bit invisible in this particular debate...

[HPH]

Originally Posted by antic

So just out of curiosity, what did anyone think of my suggestion? I feel a bit invisible in this particular debate...

I agree that there needs to be a way for us to create/download SQL Backups. Setting up your own or asking clients to setup sql servers is just asking for trouble. Is the data even encryped between the matrix sql server and the "backup" sql server? If there was a way to get the backup to your ftp space you could at least get an ssl cert and download it via https to ensure the data is secure between matrix and the backup location. Once of my clients has a ms-sql database that is mission critical and contains sensitive data (not credit card sensitive where it should be encryped in the DB, but to do nightly backups sending the data non-encryped just isn't secure). What other options is there to backup sql data other than setting up our own sql server to copy the data? I think im going to write an asp.net app to just do a select * FROM for each table then save it in an XML file. It would work but not nearly as clean as a true sql server backup.

[Brangwyn]

I think im going to write an asp.net app to just do a select * FROM for each table then save it in an XML file

Thats the sort of thing I'm afraid we will end up having, even with the other proposed scripts. A bunch of people running these backups on the servers every day potentially bogging things down with massive database selects. Actually don't forget this will probably also contribue to your bandiwdth usage twice ( once from the database to web server and then again from webserver to where ever you end up moving your backup too).

I have customers, and I'm sure I'm not alone with 300Mb databases or more, downloading that daily or even weekly really isn't all that practicle (even though we do it periodically anyway, in this instance the data isn't critical and the customer doesnt really care if they loose it all or not .. which has happened once so far actually).

Also with the other suggestions I'm weary too, someone smart enough given UNC Shares on the SQL Server can be dangerous even with the public dbo role theres still a lot you can do with some of the system storedprocedures to read information from the filesystem.

My view still is, Vortech are making backups daily .. so why can't we just have them give us an assurance that they'll be able to restore at least 24 hours prior worth of database instead of duplicating that process and also creating more resource usage on the servers in the process.

All it would take to guarantee it is someone have a checklist on their shift roster to verify the 4 sql servers have successfully backed up their files.

[HPH]

I agree that this is not a very friendly way to do things, but at the same time vortech doesn't guaranty and is not responsible if a backup fails where as I am responsible it one of my customers losses data. Doing a Select * FROM in an asp page on the same network as the sql server can't be any worse than someone at there house copying the entire db to a local sql server.

[Brangwyn]

can't be any worse than someone at there house copying the entire db to a local sql server.

I would say it probably is a little worse, doing things as you suggest, you are hitting both the webserver and database server for possibly extended periods of time (especially if the recordset is reasonably large).

I would hope that DTS is a little more intelligent than your standard ADO connection from an ASP app too (though I may be wrong there not like MS does things the smart way all the time).

[newmem]

an official word from Vortech on what they plan to do based on these suggestions would be helpful here.........Brad?

[newmem]

perhaps this is not a priority, but I hope Vortech is thinking of some way to make sure the db's are backed up properly.

On the other hand, if we insist that the end users are responsible to take the backups and download them, then we need to give them the tools for that (I surely need one for my own db).

[gadgetgal99]

Originally Posted by newmem

perhaps this is not a priority, but I hope Vortech is thinking of some way to make sure the db's are backed up properly.

Might want to wait a day or two before asking Vortech to reply, I'm sure their efforts right now are focused on the MyDoom worm that is trashing the internet.

[admin]

Originally Posted by newmem

perhaps this is not a priority, but I hope Vortech is thinking of some way to make sure the db's are backed up properly.

On the other hand, if we insist that the end users are responsible to take the backups and download them, then we need to give them the tools for that (I surely need one for my own db).

The DB's are backed up and we also showed this when MSSQL's drive took a dive. We were able to restore EVERY database but one that was created on or about the same day. We make sure all the mssql db's are backed up. We check the mssql servers once a week as well just to make sure they are indeed running as the should there as well.

[jmbeach]

For the record, on top of the backups, we should all be doing our own backups of db's anyhow. A double safety net is really important when it comes to irreplaceable data.

[newmem]

thanks for the info Brad. Was just worried if the drive in which the backups are stored also crashes then there would be a problem........

[newmem]

Originally Posted by jmbeach

For the record, on top of the backups, we should all be doing our own backups of db's anyhow. A double safety net is really important when it comes to irreplaceable data.

that's the problem.......can't do the backup and download it without the script that I had recommened (EM does not work for everyone).

Well, when you use a proprietary format, especially one designed by Microsoft, you are pretty much forced to use the tools they provide. If Enterprise Manager doesn't work for you, then there is not much more to it. If it doesn't work, chances are this or that tool won't work either. So then your stuck to going into actually writing the SQL statements yourself, which requires that you know what your doing.

With MySQL, we are able to provie phpMyAdmin, a great tool, and we could provide 20 others if we wanted. Its incredibly easy for us to backup/restore - everything is stored in files, as is the greatness of Unix.

Although MSSQL uses files, you can't copy the active mdf/ldf files or you'll get about... oh, none of the data. Also, it stores all the "active" databases in some information store somewhere (registry? file? who knows.) Just like IIS's metabase... If we lose a windows machine and have to restore from backups, its a much more difficult process since you can't just drop the files back in place. When we did NT26 and NT27, the majority of the time was the recreation of the accounts themselves using Psoft's script. Not a quick process.

On unix, I just replace a file here and there and your set.

We use the built-in backup function to create the .BAK files and have to use MSSQL EM to restore them, there is just no other way around it.