Schema Validation
danielmacey
Posts: 3
I am trying to use Red Gates software to create schema update scripts (surprise being in the SQL Schema Compare part of the forum) and I know/have found that there are a large number of invalid objects in the database.
The invalid objects can be categorised into schema objects and security objects and will be discussed separately.
The invalid schema objects are generally views and stored procedures referring to missing tables and columns. These are clearly invalid and cannot possibly be scripted.
My goal here is to find out these objects are invalid and cannot be scripted as early as possible.
One solution is to create the script, try running the script, find the first broken object, exclude it from the update list, rollback changes and start again. This solution is painful at best.
The other solution is to use SQL Dependency Tracker and use the bang icon in the bottom right of the application to identify the missing and affected objects. This seems better but is still does solve my issues. This is because the missing and affected objects are not in individual columns and I cannot output the list in a reasonable manner and the application cannot determine the affect of excluding the objects listed as affected by missing objects. So even with this method I still end up in a similar but longer try rollback try loop.
The security objects cause me issues because of invalid users and role membership.
For reasons I needn’t explain I will have to deal with invalid users in the database. I would normally deal with this by excluding them from the synchronization script however if I do then (according to SQL Dependency Tracker) the roles that the user belongs to causes the users to be included.
I cannot unselect include dependencies because I want other dependencies to be included and I cannot exclude the roles because I want the roles to exist in both databases.
I would have expected the role memberships to be part of the user object not the role object.
So what I would like to know is if there is anything obvious that I am missing that would allow me to know about problems I am going to encounter before running any scripts and what facilities I have for rectifying the problems. I would removing invalid objects may be a suitable approach in most circumstances but I still need to identify them and the effects of removing them before I take such action
Thanks and apologies for the novel
The invalid objects can be categorised into schema objects and security objects and will be discussed separately.
The invalid schema objects are generally views and stored procedures referring to missing tables and columns. These are clearly invalid and cannot possibly be scripted.
My goal here is to find out these objects are invalid and cannot be scripted as early as possible.
One solution is to create the script, try running the script, find the first broken object, exclude it from the update list, rollback changes and start again. This solution is painful at best.
The other solution is to use SQL Dependency Tracker and use the bang icon in the bottom right of the application to identify the missing and affected objects. This seems better but is still does solve my issues. This is because the missing and affected objects are not in individual columns and I cannot output the list in a reasonable manner and the application cannot determine the affect of excluding the objects listed as affected by missing objects. So even with this method I still end up in a similar but longer try rollback try loop.
The security objects cause me issues because of invalid users and role membership.
For reasons I needn’t explain I will have to deal with invalid users in the database. I would normally deal with this by excluding them from the synchronization script however if I do then (according to SQL Dependency Tracker) the roles that the user belongs to causes the users to be included.
I cannot unselect include dependencies because I want other dependencies to be included and I cannot exclude the roles because I want the roles to exist in both databases.
I would have expected the role memberships to be part of the user object not the role object.
So what I would like to know is if there is anything obvious that I am missing that would allow me to know about problems I am going to encounter before running any scripts and what facilities I have for rectifying the problems. I would removing invalid objects may be a suitable approach in most circumstances but I still need to identify them and the effects of removing them before I take such action
Thanks and apologies for the novel
Comments
2) Unfortunately there isn't an easy solution to the users problem at the moment - whilst 'ignore users' permissions and role memberships' will avoid synchronizing the role memberships, the users are still picked up by 'include dependancies' despite this.
Would 'ignore users' permissions and role memberships' causing 'include dependancies' to not pick up the users as dependant objects for the roles fix your problem here? If so, I'll add this forum thread to the bug we have open for that (which will increase its chances of getting fixed).
Redgate Software
2) Choosing the ignore option “Users’ permissions and role memberships†does what I require. I am not sure how but I completely missed that one.
For some reason I was expecting the ignore option “Permissions†to do that but it does make a lot of sense they are separate options.
Thanks