Subscribe

Backup more than 1000 sql databases

Hello all,

IHAC that wants to backup more than 1000 sql databases in one instance. It has one volume for user databases with one lun; he also has update the snapmirror within SnapManager but the process creates 5 subprocesses with 255 databases at a time.

The questions are:

- Each subprocess "freezes" 255 databases and creates an snapshot, so 5 total snapshots at the end of backup process. Are all databases consistent at DR site?

- I recommended to split this amount of databases into different volumes with one lun per volume. The rule is 35 databases per lun, but I don't think customer accepts that (it suppose aprox 50 volumes and luns). Is recommended to create 5 volumes in order to allow about 255 databases per volume?

- The application creates/deletes databases in a regular basis and is necessary to run the wizard in order to inform SMSQL which databases have to be copied. Is any way (CLI command) to run this wizard in an unantended way?

Thanks a lot for your help. I'm not experienced in SMSQL and I'm quite lost in this project.

Cheers,

Re: Backup more than 1000 sql databases

Hi Jordi,

This is an insteresting case. Could you tell me a little bit more about the customer environment? it'll be good to understand the bigger picture here.


Meanwhile, the following are my thoughts: -

- Each subprocess "freezes" 255 databases and creates an snapshot, so 5 total snapshots at the end of backup process. Are all databases consistent at DR site?

SOURAV> Each snapshot is a consistent backup of the 255 databases that it freezes. This means that you can only restore those 255 databases that correspond to a snapshot usig that snapshot. SMSQL will figure this part internally so there is no reason to worry. The only pre-requisites in your case for a DR are as follows: -

   i. The DR site should also have SMSQL on it and only SMSQL should be used to recover the databses.
  ii. SnapMirror the SnapInfo volumes as well. You need to recover the volume and the appropriate LUNs at the DR site and then initiate an SMSQL restore.

- I recommended to split this amount of databases into different volumes with one lun per volume. The rule is 35 databases per lun, but I don't think customer accepts that (it suppose aprox 50 volumes and luns). Is recommended to create 5 volumes in order to allow about 255 databases per volume?

SOURAV> Well, that still does not solve your problem. The 35 database limitation is a Microsoft one. Please refer the following KB article for more info: -
http://support.microsoft.com/kb/943471

- The application creates/deletes databases in a regular basis and is necessary to run the wizard in order to inform SMSQL which databases have to be copied. Is any way (CLI command) to run this wizard in an unantended way?

SOURAV> That is not an absolute necessity but is strongly recommended. Incase you observe problems for not doing so such as DB's not listing properly or corrupt backups etc. then please run the Config Wizard again. Unforunately that is not an operation that you could do using the CLI.


Regards,
Sourav.