Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Just a rant about how third parties design apps Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 1:59 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 985, Visits: 1,833
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!
Post #1338156
Posted Tuesday, July 31, 2012 2:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 12,905, Visits: 32,182

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1338163
Posted Wednesday, August 1, 2012 7:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 985, Visits: 1,833
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.
Post #1338497
Posted Wednesday, August 1, 2012 9:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1338933
Posted Thursday, August 2, 2012 7:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 985, Visits: 1,833
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.
Post #1339140
Posted Thursday, August 2, 2012 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1339173
Posted Thursday, August 2, 2012 8:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 985, Visits: 1,833
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.
Post #1339252
Posted Thursday, August 2, 2012 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1339254
Posted Thursday, August 2, 2012 9:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 985, Visits: 1,833
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?
Post #1339258
Posted Thursday, August 2, 2012 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1339273
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse