Deploy SQL Server Backup Maintenance Plan in multi-tenant environment

We have a multi-tenant environment in which I've been trying to deploy a standardised maintenance plan across all servers. I've been running into issues importing the maintenance plan properly, as well as issues setting up the correct minimal permissions to run the job.

Can anyone offer some guidance on how such a backup maintenance plan can be deployed across a heterogeneous, multi-tenant environment, such as ours. I've detailed my attempt below, and given the level of complexity is quickly rising, I'm wondering if I'm heading in a completely incorrect direction for my situation.

Is there a better way?

I'm looking for a solution that can be easily deployed via script/SQLCMD, so I can roll it out via Puppet. I'm also hoping to stick with Maintenance plans as SQL knowledge is low in our team, and having future edits to the backup plan require in depth T-SQL knowledge would be problematic (though it seems like a T-SQL script or to in order to run the backup may be the simplest way out).

The Environment

• Right now we run mostly Windows 2008 R2 with SQL Server 2008 R2 Standard

• However, a solution that supports Windows 2012 and SQL Server 2012/2014 would be the aim

• We support many SQL Servers for multiple customers, all of which are isolated.

• Some customers run on their own Active Directory forest, whereas others run stand-alone SQL Server with no AD. So there's no common factor in terms of AD authentication.

• All customers are isolated into their own network segment, but we can poke holes in the firewall for backup purposes.

• Right now we use Puppet for basic configuration management across our servers. Basically this translates into an easy ability to deploy new files, run scripts, SQL (via SQLCMD) and setup permissions/users on servers in a standard way.

What I've Tried

Right now, my main attempts have involved exporting my maintenance plan to a DTSX file via integration services, and the related T-SQL for the job Subplan. Then importing these i

Agree with Jon.

Instead of going through the pain of setting up Maintenance Plans, I highly recommend to use

Ola Hallengren's SQL Server Maintenance Solution

This solution is flexible (can be adjusted as per your needs) and is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012 as well as it is widely used in SQL Server community.

We have a much more complex environment and we use backup as well as maintenance solutions from Ola's site.

StackExchange Database Administrators Q#55865, answer score: 5