Wednesday, March 28, 2012

How to remove all non-system service broker objects

I'm looking for a tool or script that can wipe a database clean of any and all user configured service broker objects.

I've got two environments, Development and QA, that I need to have parallel service broker configurations. Each environment is hosted on a different set of servers. I need to make sure the Development environment looks exactly like the QA environment from a Service Broker perspective (other than the specific service broker instance references in the installed routes). I've got a script to build the objects that I want, but the Development environment is full of artifacts of abandoned experiments. Before running the creation script in Development, I'd like to start with a clean slate. Recreating the Development databases is not an option.

If a tool or script is not available, I know I need to adress at least the following object types, but I'm not sure if this list covers everything or in which order I need to drop which objects: routes, remote service bindings, certificates, database principals, tcp endpoints, services, queues, contracts, and message types

Thanks in advance

Lee

I would suggest to create a script that drops all types of objects (services, contracts, message types, queues, routes, remote service bindings). One easy criteria to choose wheter is a 'system' object or not is to look if a similar object (same type and name) exists in [master]. If it does, then is 'system'. Each of these types has a coresponding metadata view: sys.services, sys.service_contracts, sys.service_message_types, sys.service_queues, sys.routes, sys.re:mote_service_bindings.

Attempting to drop a true 'system' object would fail. But there is one object that is implicitly created, but is not trully system: the 'AutoCreatedLocal' route in each database. this route can be altered and dropped, it is not a system object.

HTH,
~ Remus

No comments:

Post a Comment