Scripting database objects 1

[equus2]

Hi,

Once I started thinking about this, I realized I don't really know how to do this.

Basically, we have a disaster recovery server that will be kept up-to-date via a 3rd party log-shipping tool.

But what about non-user database objects, such as

1.) jobs
2.) operators
3.) linked servers
4.) maintenance tasks
5.) backup devices
6.) logins

How does one keep these up to date in a timely, easy fashion? I know that you can make a backup of msdb to address some of these objects, such as jobs and packages, *but what about operators, maintenance tasks, linked servers, logins?* - are these part of msdb as well? Or separate?

I'm confused now just thinking about it.

Thanks

 

[mrdenny]

Operators, Maintenance tasks, and backup devices are stored in the msdb database.

Linked servers and logins are stored within the master database. These objects shouldn't change that often and it's usually easier to simply create them manually on both sides.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[equus2]

Thanks

 

[equus2]

So, do you think that doing a backup and restore of the msdb db across servers is the best method for keeping the disaster recovery server in sync (jobs, pkgs, etc)

Thanks

 

[mrdenny]

That would be one option. If your jobs change a lot that might be best. After you finish the restore don't forget to disable the jobs, and update the originating_server column with the local server name.

If the jobs are fairly static I'd just handle the job deployment manually. That's much easier than dealing with restoring a system database every day.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[iamtechuser]

Ok, thanks...

 

[iamtechuser]

one more thing, though...

while I can do this manually, I'm just wondering if there's a better way... can't help but wonder if I can automate this... afterall, it may be difficult to keep track of what's changed with many different developers, it would be very easy to overlook something...

thanks

 

[mrdenny]

Your best bet would be to setup SQL Profiler to monitor the system for these changes. That or stop developers from making changes to these settings without going through a change control procedure.

As you can't put triggers on system tables (or views if SQL 2005). Your only other option would be to write something which exports all the data to the backup system daily and compare it to the secondary system making any changes which are needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[iamtechuser]

Thanks for the info

Have one more question, this is for 2005.

When I query the sysjobs table using "select * from sysjobs"

there is no originating_server column, only an originating_server_id column and it's "0" for all jobs.

Doing a little poking around:

(select distinct object_name(object_id) as table_name, name as column_name from sys.all_columns
where name like '%originating%')

I see that the sysjob_view *does* have an originating server column, but it's obtained from another view with the following query:

select UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) AS originating_server

So is it really necessary to update this column anywhere in 2005?

Thanks!

 

[mrdenny]

In SQL 2005, no you shouldn't need to change it because it's using related data. In SQL 2000 and below they stuck the server name in there instead of the Server ID from the master.dbo.sysservers table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog