July 31, 2012 at 1:59 pm
And by third parties, I mean of all parties, Microsoft themselves. If a column needs to be added to a table, you'd think it would be as simple as executing ALTER TABLE ADD <column info>, right? Except that in at least two cases this is not what Microsoft does. In CRM and in, of all things, scripts generated by SSManagement Studio, MS has the system create a copy of the old table, copy data from old to new, drop old, rename new. Uh, WHY? This is about as inefficient as it gets and is a disaster on very large tables. And worst of all, replication will not work with this approach requiring replication to be deleted before a CRM deploy and re-added after the deploy.
So the rant is, these developers should know better. What on earth possesses them to take lame shortcuts to make it easier on them while shifting the pain to the production end? Yes, I have hit this situation at work and until we go to AlwaysOn in 2012 there's nothing I can do about it.
Rant over. Anyone who knows the reasons why such things happen feel free to say so and remove the feeling of helplessness generated by such practices. ARGH!
July 31, 2012 at 2:05 pm
the reason is to make is a beauty of a gui/user tool.
yes for a simple add column, it's a trivial one line wonder in TSQL.
But they put logic in place that handles everything you can imagine: dropping and recreating constraints accross multiple tables, migrating the data, and all that behind the scenes so you don't have to.
things like moving a column, changing a primary key, more advanced things all require more than a one liner script;
as a result, all changes fromt he GUI go thru the advanced process...yes, it's more rounda bout, but it covers all the bases.
Lowell
August 1, 2012 at 7:09 am
Lowell (7/31/2012)
the reason is to make is a beauty of a gui/user tool.yes for a simple add column, it's a trivial one line wonder in TSQL.
But they put logic in place that handles everything you can imagine: dropping and recreating constraints accross multiple tables, migrating the data, and all that behind the scenes so you don't have to.
things like moving a column, changing a primary key, more advanced things all require more than a one liner script;
as a result, all changes fromt he GUI go thru the advanced process...yes, it's more rounda bout, but it covers all the bases.
And it basically blows up replication in the process. It would be one thing if you could turn it off in these interfaces. But you can't and so we have to do hours of replication rebuilds because someone wants to add a column in CRM. Utterly ridiculous.
August 1, 2012 at 9:38 pm
It's a least common denominator approach. If SSMS were coded to account for every permutation possible to arrive at the most efficient way to add a column think about the design, the testing and the support when new schema features are introduced. Now, all of that said, they have most of the technology worked out to do it and it has been part of Database Projects since VS2008 incl. VS2010 and SSDT for 2012, however afaik Database Projects still do not account for Replication. That is quite a tall task you're expecting. Maybe I missed it, but why even bother with SSMS if you know the issues and know how to work around it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 7:18 am
opc.three (8/1/2012)
It's a least common denominator approach. If SSMS were coded to account for every permutation possible to arrive at the most efficient way to add a column think about the design, the testing and the support when new schema features are introduced. Now, all of that said, they have most of the technology worked out to do it and it has been part of Database Projects since VS2008 incl. VS2010 and SSDT for 2012, however afaik Database Projects still do not account for Replication. That is quite a tall task you're expecting. Maybe I missed it, but why even bother with SSMS if you know the issues and know how to work around it?
1) Because I am a production DBA and I have to deal with developers who depend upon tools to generate code, and they have been known to turn this in for a table that is, say, 100 GB and then ask me why it takes so long to add a column or why they are running out of disk space. 2) Because CRM does the same thing on a production DB and there is no other way to update the CRM schema -- and replication is a requirement. And what special technique is there to take an "add column" condition and generate the same ALTER TABLE script that has been standard for over 15 years? You either add it as NULLable or you enforce adding a DEFAULT at the same time you add a NOT NULL column. How many permutations are there to that?
That said, we can't move fast enough to SQL 2012 next year. That is the only bandaid I know of, because most of our replication requirements will no longer use replication. AlwaysOn and read only replicas will do the same thing without the replication requirements.
August 2, 2012 at 7:49 am
jeff.mason (8/2/2012)
opc.three (8/1/2012)
It's a least common denominator approach. If SSMS were coded to account for every permutation possible to arrive at the most efficient way to add a column think about the design, the testing and the support when new schema features are introduced. Now, all of that said, they have most of the technology worked out to do it and it has been part of Database Projects since VS2008 incl. VS2010 and SSDT for 2012, however afaik Database Projects still do not account for Replication. That is quite a tall task you're expecting. Maybe I missed it, but why even bother with SSMS if you know the issues and know how to work around it?1) Because I am a production DBA and I have to deal with developers who depend upon tools to generate code, and they have been known to turn this in for a table that is, say, 100 GB and then ask me why it takes so long to add a column or why they are running out of disk space. 2) Because CRM does the same thing on a production DB and there is no other way to update the CRM schema -- and replication is a requirement. And what special technique is there to take an "add column" condition and generate the same ALTER TABLE script that has been standard for over 15 years? You either add it as NULLable or you enforce adding a DEFAULT at the same time you add a NOT NULL column. How many permutations are there to that?
I was thinking of:
- the times people add columns in the middle of a table
- or add a column and add it to the PK in one shot
- and setups where replication is involved
Those are three common ones off the top, the first two of which SSMS code tries to deal with, plus the two you mentioned. If we brainstormed a bit I am sure we could get a good more number of cases on paper.
Speaking of replication, even if SSMS did account for it when adding a column it would mean you would have to have replication setup in dev exactly as you have it in prod so the servernames would come out proper in the proc calls which would likely be impossible unles you had a dev domain where the server names mirrored prod.
That said, we can't move fast enough to SQL 2012 next year. That is the only bandaid I know of, because most of our replication requirements will no longer use replication. AlwaysOn and read only replicas will do the same thing without the replication requirements.
Either that or grind out the behavior coming from the dev group. You could always attempt to train them on how to properly add a column, work with management to implement a code review process and reject scripts that rebuild tables for a simple column addition.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 8:58 am
If someone wants to add a column to the middle of a table, they should get better SQL training. If order matters that much, make a view and change the view when needed. To me, column order doesn't matter. As for replication, if you do it by the book (i.e. you don't care about column order and you aren't changing primary key, and if you are changing primary keys on a production system you already have bigger problems than replication), schema changes are seamless since 2005. You no longer need sp_add_repl_column. Normal ALTER TABLE scripts pass. Therefore you don't need replication on dev unless you have to test scripts that drop tables. Dropping tables is the only schema change that replication can't handle.
As far as exerting pressure on developers, we are a Fortune 25 company, with about 500 SQL Server instances, 6000 databases, and hundreds of apps. We have very little ability to push against poor practice on a systematic level. But that's not that common a problem, and our current issue is against a system made by Microsoft. Microsoft CRM and replication do not mix in the least unfortunately. So we are going to have to mandate once we start installing SQL 2012 in live that CRM must go onto SQL 2012 going forward.
August 2, 2012 at 9:01 am
jeff.mason (8/2/2012)
If someone wants to add a column to the middle of a table, they should get better SQL training.
This is exactly what Microsoft is up against. They have to provide for all things from all comers. Someone of your skill level is not their target audience when it comes to the Table Designer 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 9:03 am
opc.three (8/2/2012)
jeff.mason (8/2/2012)
If someone wants to add a column to the middle of a table, they should get better SQL training.This is exactly what Microsoft is up against. They have to provide for all things from all comers. Someone of your skill level is not their target audience when it comes to the Table Designer 🙂
So what's their excuse for their own CRM product?
August 2, 2012 at 9:15 am
jeff.mason (8/2/2012)
opc.three (8/2/2012)
jeff.mason (8/2/2012)
If someone wants to add a column to the middle of a table, they should get better SQL training.This is exactly what Microsoft is up against. They have to provide for all things from all comers. Someone of your skill level is not their target audience when it comes to the Table Designer 🙂
So what's their excuse for their own CRM product?
It sounds like you may be trying to fit a square peg in a round hole, i.e. misusing replication in your CRM environment. Move to 2012 as soon as possible or adjust your approach.
From whitepaper Sharing_Data_Across_CRM_Deployments_WP.pdf:
SQL Server Replication can be used for scenarios in which a CRM system needs to share readonly
copies of data with other CRM systems. This technique is applicable only for scenarios in
which entities have a static data schema and a limited number of relationships; it is not
recommended for sharing transactional data. This approach also requires that the schema of
the shared data be the same on the Publisher and Subscriber CRM systems. It is important to
note that if the schema of the data needs to be changed (via CRM customization), the
replication relationship has to be suspended, customizations applied on both the Publisher and
Subscriber instances, and replication relationship reestablished.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 9:19 am
jeff.mason (8/2/2012)
opc.three (8/2/2012)
jeff.mason (8/2/2012)
If someone wants to add a column to the middle of a table, they should get better SQL training.This is exactly what Microsoft is up against. They have to provide for all things from all comers. Someone of your skill level is not their target audience when it comes to the Table Designer 🙂
So what's their excuse for their own CRM product?
oof their excuse?
you've identified that your developers use the wrong tool for the job, need better training on how to do it better, but blame microsoft for the ramifications of using a "catch all possibilities" application for making your replication suffer?
I realize it's a rant, but i'd still think this is more of a training issue; there's usually multiple ways to do the same job in SQL Server, it's up to us to pick the most appropriate for the job onhand.
Lowell
August 2, 2012 at 10:23 am
So why bother making replication able to replicate schema then? There is only one schema change that replication cannot handle -- dropping tables that are part of a publication. Why would Microsoft program CRM to drop tables to add a column? Why would you ever want to do that? Ignore exceptions (that in my opinion are bad form anyway) -- what possible reason is there for a professional product to drop a table whenever you want to add a column? That's the core of my rant.
August 2, 2012 at 10:25 am
And Lowell, I am blaming Microsoft for creating an application that uses SQL Server as its data repository that doesn't use their own best practices in maintaining its own database. Which given how SharePoint breaks all sorts of best practices is not an anomaly there.
August 2, 2012 at 10:43 am
jeff.mason (8/2/2012)
So why bother making replication able to replicate schema then? There is only one schema change that replication cannot handle -- dropping tables that are part of a publication. Why would Microsoft program CRM to drop tables to add a column? Why would you ever want to do that? Ignore exceptions (that in my opinion are bad form anyway) -- what possible reason is there for a professional product to drop a table whenever you want to add a column? That's the core of my rant.
I think the CRM problem is an extension of the problem you have with SSMS. MS was all but cornered into making the Table Designer be the way it is with respect to schema changes for the reasons mentioned earlier, and CRM seems to follow that same least common denominator approach. Do I like it? No. I get the starting point of your rant but for me, knowing why something I do not like is the way it is sometimes makes it easier to accept 😉 At least it is documented.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 20, 2012 at 11:47 am
In my experience, SSMS does not script a drop and recreate of a table unless you insert the new column somewhere other than at the end. To append a new column doesn't recreate the table. I know this because I always have the save change script option active so I know what is going to happen before I commit the changes.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply