Native sql backups and SMSQL setup recommendations

[sssdbasssdba]

Currently we are setting up a multi instance failover mssql cluster with 100+ smaller databases running on mssql 2008 R2 attached to a NetApp appliance we have a duplicate DR environment sitting in a remote location and we have a requirement to take 30 minute transaction log backups  to this duplicated DR environment.

There is also a requirement to take occasional native backups off the NetApp environment to other machines (vendors, training db’s and developers)

Can you please advise if taking native backups will disrupt the transaction snapshots and if it does how do I get a copy of the mssql database as a .bak file?

Also what are the possible issues with a setup like this that we should be aware of and what would the recommended setup be for making use of the NetApp Appliance and native sql backups?

[sssdbasssdba]

after a very large learning curve i have answered the questions myself the snap products have limitations

Yes this is possible to do both mssql copy only backups (they mssql backups MUST be copy only) and snapshots but there are  some limitations with doing this

1) you must not overlap mssql copy_only backups with a snap manager task as this will cause snapmanager to wait and will attempt to load the snaps into memory (i presume its loading the snaps into memory as process uses as much memory as it can)

2) if you do not take copy_only backups this will cause the LSN chain issues with the databases concerned

3) if you have multiple mssql instances you can only run one instance of snapdrive at anyone time (a limitation of the snapmanager product) so scheduling is difficult

4) taking snapshots from snap manager for sql scheduled jobs works just fine until a database is added to mssql server and snapmanager cannot add it to the job so you MUST script out the backups and run them from sql server or delete the job and re schedule the snap job.the issue here is that if the snaps fail the mssql job is successful but the snapshot is not in a consistant state but mssql reports successful job .

(i hate running .exes from mssql but there is little option)

Please let me know if you need further details

[ADAMSCOOP]

Thinking of point four. Rather than running scripts inside SQL to call an exe, why not run scripts through scheduled tasks that query SQL and then generate the command and run the exe? Resolves the issue of running exe's through SQL agent.

Adam

[sssdbasssdba]

Thanks Adam

We have been running snap manager for sql snaps now for around a year now and have proved to be an excellent technology the initial learning curve was steep but the benefits are vast.

I have kept the running of snap manager jobs within the sql agent and have scripted out a solution to run some tests on the sql jobs to check for “short running snap jobs” this has proved very successful to check if there is a failure with the snapshot job (along with mounting a copy on a remote server for verification) . The other script I run is a query against mssql to make a list of the databases that exist then dynamically generate a snapshot backup command with all the databases that exist at the time of the snapshot and then run the generated script.

To get around most issues with native backups we mount flex clones to a remote server and take native backups from the clones (once scripted is very little effort).

another option is to have a snapshot free window where you run native mssql copy only backups so then you do not need to mount the clones on a remote location .