Subscribe

Automate add and removal of SQL Databases with SnapManager

I'm really new to much of this being that I am a DBA but we are using SnapManager for our backups primarily and for a DR solution. As part of that when we add or remove databases we will have a NetApp backup failure due to the new / removed database not being present. I'm wondering if there is a way to automate the SnapManager Configuration Manager so that the removal / addition of a database will not cause issues or wondering if we only make the SQL job that runs the NetApp backup dynamic to only hit active databases if that will be sufficient. I'm pretty sure I tried just removing the database from the list in the SQL job in the past and still had the failure so it seems that we have to run Configuration Manager. Being that our schedule is to take snapshots every hour the window is pretty small for a database to be added / removed and for CM to be run all in that time.

Any thoughts / suggestions woudl be greatly appreciated.

Automate add and removal of SQL Databases with SnapManager

David,

I ran across this post a while ago:

http://communities.netapp.com/message/24470#24470

and converted all of my SMSQL jobs to that format (C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup  –svr '<servername>'  -d '<servername>\<instance>', '0' ...) and everything works well.  We can add/remove databases from those servers\instances without adjusting the SMSQL jobs (this assumes that the databases are added  such that they follow the constraints of SMSQL requirements, in our case, separating the datafiles and TLogs on separate LUNS).

Hope this helps!

Thanks!

Jason

Re: Automate add and removal of SQL Databases with SnapManager

We also have standard-tasks to backup our servers:

new-backup  –svr '<svr>\<ins>'   -RetainBackupDays  7 -lb -bksif -RetainSnapofSnapInfoDays 7 -trlog  –mgmt standard  -ArchiveBackup  -ArchivedBackupRetention Daily

But is there a way to update the automatically update the protection (dataset) information?

Automate add and removal of SQL Databases with SnapManager

Thanks for that tip Jason. Does that preclude you from having to run Configuration Manager when the database is added or dropped? Is that what Adrian is referring to when stating "protection (dataset) information"?

Thanks again for the help and information.

David

Automate add and removal of SQL Databases with SnapManager

David,

Once we changed the SMSQL jobs to run with that set of parameters "-svr <servername> -d '<servername>\<instance>','0'" we have not had to run the configuration Manager when adding/removing databases (again, making sure that the databases we're adding conform the proper disk layout with datafiles on our datafile LUN and tlog on our transaction lun).

I'm afraid I don't quite understand what Adrian is referring to with the "protection (dataset) information".  Presumably it's the "standard"  in the -mgmt parameter.  If that's the case, I don't have any information on that because we haven't changed any of that.

Hope this helps!

Thanks!


Jason

Automate add and removal of SQL Databases with SnapManager

It's definitely worth giving it a try. Thanks again!

Automate add and removal of SQL Databases with SnapManager

Adrian is referring to protection manager datasets,

The easiest way to check dataset configuration is to look in the SMSQL_SnapInfo dir and look at the xml file that contains the configuration.  This will list all databases included in the dataset.

Again provided the database are in the same luns as already configured databases then they should be archived or mirrored appropriately, if they exist in new luns then you will need to rerun the configuration wizard to make sure the data gets added to the protection manager dataset.

Re: Automate add and removal of SQL Databases with SnapManager

Yes, I mean Protection Manager Datasets.

Is it allowed/supported to edit the xml-file manually? like a scheduled powershell script, which checks the instances for new databases and adds them to the xml?

Re: Automate add and removal of SQL Databases with SnapManager

Hi,

In case of SnapVault:

I'm not able to provide you the scripts the are linked with monitoring / Scheduler and it's very difficult to extract the part of Database list.

To inculde new database to the backup set you can modify the file  "SnapInfo__$Serveur__DataSet.xml" with 2 or 3 Scripts.

One script to list all db in your instance.

SQL Script:

-- print "Hello"

SELECT (@@SERVERNAME), sysdb.name,

--CASE sysdb.status

sysdb.status ,

--WHEN (512) then 'Offline'

--WHEN (528) then 'Offline'

--ELSE 'Online'

--END,

sysdb.dbid,

DATABASEPROPERTYEX(DB_NAME(sysdb.dbid),'Recovery'),

RIGHT(convert(varchar, getdate(), 112),2)+LEFT(convert(varchar, getdate(), 101),2)+LEFT(convert(varchar, getdate(), 112),4)

FROM master.dbo.sysdatabases sysdb

A Script to genrate the XML file:

$entete_file = '<?xml version="1.0"?>

<SMSQL_DataSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <DataSetCollection>

    <DataSets>

      <SQLServer_Instance>'.$Serveur.'\\'.$Instance.'</SQLServer_Instance>

      <DataSets>

        <DataSet>

          <DataSet_Name>SnapMgr_SQLServer_'.$Serveur.'</DataSet_Name>

          <DataSet>

';

Generate the file's body

#Creation footer du fichier xml

#

$footer ='           </DataSet>

        </DataSet>

      </DataSets>

    </DataSets>

  </DataSetCollection>

</SMSQL_DataSet>'

;

Regards