August 24, 2010 at 8:29 am
joshusch (8/24/2010)
From the editorial: "There are times that you might be tempted to change things in a database built a software company, or add objects to that database. In many cases this can be seen as action that voids any support contract with the vendor,..."As soon as you touch it, it's no longer theirs, it's yours. As a software company, why would you want to provide support for someone else's random database? Just my $0.02
Not sure I agree here. I've had more than a couple systems that indexed every field. My removing a few indexes hasn't usually affected the software and it's made it run better.
I've also see the reverse. No indexes, or almost none. Adding a few can improve performance.
August 24, 2010 at 8:30 am
RobertYoung (8/24/2010)
....Adding modifying functionality will not only be dangerous, but also tick off the vendor; you're stealing from their rice bowl (or so they feel). Even if you pull it off, the relationship will be strained from then on.
What if you feed back changes to the vendor? Allow them to learn and re-use those changes down the road? I've actually had good luck in a few places by "training" the vendor developers on something that would improve the product.
August 24, 2010 at 8:49 am
nick brandwood (8/24/2010)
Steve,I work for a company that produces software managing the Offer-To-Invoice phase Bid and Tender management, with some very large multinational companies managing hundreds of millions of dollars of offers using our systems. We also have a blanket "Do not touch the database" rule with our clients.
Thanks for responding.
I think there are two issues that are involved in this idea.
The first is that even though the EULA usually specifically denies suitability, a supplier often has a non written contract with their client that the software meets the customers needs and is reliable. In a case like ours, the only way to keep a customer is to ensure their business is safe in our hands and a blanket "hands off" is the only way to protect oneself from blame when customers damage the database.
I would agree with this. Customers can damage the db, and you should not be liable. However I think there is some room to create exceptions here without a blanket policy. Some "approved changes" can come through.
What if an client created index clashes with a new index in a future release? Should the upgrade scripts test for every possible variation that may have occured, or can it presume the structure is standard?
Obviously naming can be an issue. However I would like to think that you have some error checking in scripts. Not accounting for everything, but an object collision ought to be logged and either rollback the upgrade or log it.
How much of our time will be spent debugging why in a particular installation everything has gone horribly wrong but works everywhere else. ...
Time spent here should be billable to clients. Perhaps an hour or two is part of maintenace/support, but beyond that clients should be responsible. I'm not asking for blanket support.
My experience is that clients are usually unwilling to accept that a problem is their end, and any modifications made are usually forgotten or invisible to the supplier, and it is then the supplier who has to investigate and demonstrate that issues are not their fault.
My experience is similar, though I'd add most vendors seem to want to blanketly assume their product works perfectly and not accept criticisms of half-***ed design.
That does not mean, however, that we refuse suggestions in ways in which we can improve our database's configuration *. We have in the past worked with DBAs from our larger clients when they have reported performance issues and had suggestions on how alleviate the problem. In fact these suggestions have led to modifications made available to all customers, of benefit to all.
Completely agree here. I've gotten developers on the phone and explained a security issue or an indexing improvement and they have accepted it. However I've had more companies refuse to listen to suggestions.
Nick
* If I can hijack your thread I would ask another question: Do you think that a software supplier should budget for an experienced DBA to manage the database structure? From my 15 years experience in software development in small companies the most successful projects have had a proficient DBA instead of a systems technician manage the database. So why is this so often overlooked - because they do not provide invoicable hours, sellable features or tangible benefits?
With regards to your last items. Yes, if you build software on a database, you ought to have a DBA help you review design and improve performance. I don't know if this needs to be a FTE, but at the least some design review and suggestions/comments/criticisms should be sought.
I think this is often overlooked because many software products are the brainchild of a couple smart people. They start coding, solving issues, with limited budget (independent or inside a company). By the time the product is viable, they are too invested to make lots of changes, and often don't want to spend the time/money to refactor the design.
Probably a touch of arrogance as well.
August 24, 2010 at 8:49 am
The product I work on is an enterprise application and our clients pay us for a support contract. That contract doesn't cover customizations but that doesn't necessarily mean that we won't work on an issue involving a customization. You will just likely get a bill for it. Sometimes that's waived, if it doesn't involve much or a few other situations, but we do our best to make it clear that officially we're supposed to bill for it so the client (at least the individual person we're working with) is aware that it wasn't an issue with our product. Part of the reason we took this approach is a lot of referrals we get come from the end user talking to their peers in other companies and while the root cause may not be with our product to the end user it clearly is an issue with our product.
August 24, 2010 at 8:56 am
Steve Jones - Editor (8/24/2010)
Nelson Petersen (8/24/2010)
Some questions come to mind:1.) If the purchased application is poorly designed or inadequately indexed, who made the decision to buy that software? The decision making process should be revisited and the decision maker(s) should be trained.
The options for software remain unchanged: you can write your own, purchase and customize if the vendor permits that, or
purchase and live with the design and support that you pay for.
Quite a few purchasing decisions are made by features and budget. The accounting group needs xxx, and of their software choices, a couple fit. When they arrive, you may realize that performance is not great, or there are issues. What do you do? Return the software? It's a large investment in time, and once the decision is made, often on incomplete information, it's not going to be returned.
I'm considering getting involved with an application which runs on Open Source infrastructure (not SQL Server, no surprise), and is OS itself. Any changes go back to "them" to be used, potentially, by other clients. But they have a warning that they neither condone nor support, etc. changes to the base schema. OTOH, they have an established "side" schema where clients and VARs are free to play. There is also an established procedure to get client/VAR changes to the base system integrated into the base system. I don't know how well that works. Seems to be a fair compromise.
August 24, 2010 at 9:40 am
My first exposure to SQL Server was to use to import data from 3rd party datasources that we weren't allowed to touch but we needed to report from. The 3rd party datasources were other SQL Servers and Informix databases.
Over the years I've kept a hands off approach to 3rd party databases with the exception of setting up my own backup strategies and a very occasional index tweaking, sometimes with the blessing of the 3rd party. Someone else stated that usually the application code is written by app developers and not by database people, I agree with that. I've approached a few of these companies with suggested index changes and have seen some of them take the feedback well and put it into future releases.
In my current job I support 30 sql server instances that come from the same 3rd party. The application is very specific to my industry and core to our business. They don't have a DBA and take some of my advice well however they insist on the practice of truncating the transaction log just before a full backup. To keep myself compliant with their support I have had to comply (with gritted teeth) to their standard. However, slowly, they are comming around to understand why this is a bad practice. I started out by writting up why this is a bad practice in a 3 page word document with plenty of references and distributed this to their development team. I've also passed this info up to the people who have the business relationship in my company. I believe, in the next release, I will get my way and will be able to NOT break my transaction chain every night.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
August 24, 2010 at 10:02 am
Steve,
I think that "approved changes" is the key to your first phrase and yes, in that case I would be happy to accept them. Approved by us. Try it in the staging environment, write me an email "hey nick, if you put an index here you get 20% better performance!", "Good idea, next release'll have it."
Naming was the first issue that came to mind. other issues may occur that are more difficult to detect. Yes, we could bill them for having "wasted" our time finding an issue we didn't make. But then (perhaps more so in Europe) good will from the supplier to the client means that a most of the hours spent on support are never charged no matter how absurd the issue was. When a client calls for support they're usually unhappy. Fixing the issue makes them happy again. Happy clients pay maintenance fees. You usually get support requests from users who have no idea what "your DBA did xyz" means, or know if we're making it up. Blaming someone else will create a negative image, and to be honest they don't really care what was wrong, just as long as it is fixed and doesn't happen again. But then it greatly depends on what type of software you produce etc. This is possible if you keep your support costs down, and you can do that by ensuring the application installed is the one you installed.
My experience is similar, though I'd add most vendors seem to want to blanketly assume their product works perfectly and not accept criticisms of half-***ed design.
Touchè. Though I'm happy to say that this isn't the case where I work - But then, would adding an index fix that? Terrible design requires reworking more than one layer I assume so not sure it is in scope. They are probably suppliers who you will have happily replaced (or IT pushes for replacement) with a new product when one is offered. Maintaining the relationship between supplier and client is very important if after 12 years you want to still the the leading supplier in your field. Choose your suppliers better, not on how well they are at application design, but how interested they are in you and working with you. A software who puts existing clients on their web-page are basically saying "call them and get a reference", we're good to work with.
I think that long term application development requires a full time FTE and that software houses do not want to employ them because they cost too much for the day-to-day visible benefit they gain. I'm not sure if it really is arrogance, I think it is ignorance. I have always pushed for a full time DBA to manage the database side and have had more no's than yes's. It scares me when we hire developers and they profess to know "SqlServer, Oracle and MySql. Any relational database". To the person hiring this means ok, we don't need the DBA.
I think have had enough experience with Sql Server and Oracle to say that I don't know them at all and would happily turn it over to someone who does. Its just a case of justifying it in the budget.
Perhaps what is required is education in how very complex the systems actually are (rather than publicity on how simple and user friendly they are). Perhaps articles that justifiy the DBA in the software house would prevent the problem at the source.
In essence, I think that you should not have to be looking for ways to modify your 3rd party database, the supplier should have already found them. If they haven't then you should push your supplier to undertake a performance review with you where you can suggest how they improve the situation. Thats basically a free DBA for a week or two. How you present the idea is critical. "The software is cr*p, my mum could make it faster" won't work as well as "perhaps we can look this together to improve it".
If the supplier is not interested or is too arrogant to listen then I think you should consider changing supplier, if that is possible because they are clearly not interested in ensuring their tool makes you are the best at doing whatever it is you do.
August 24, 2010 at 10:34 am
Fair points, Nick, and it's a tough battle. Fairly rarely does IT have significant input into choosing the suppliers, at least from what I've seen in the US. It's typically the salesman putting on a good show for the people that can sign the checks, dismissing or minimizing complaints from IT.
I'm not sure long term software development needs an FTE. I think a good consultant for one day a month, or a couple half days, could provide some guidance, and education, that might improve things all around.
I have definitely had luck calling the support people with a "hey, I noticed this wasn't working well for us and I thought that if we did x, things would improve." You'd be surprised how often better indexing makes a huge difference.
August 24, 2010 at 10:35 am
I support an application that was selected by the business client, without input from Technology. This lack of technical insight resulted in their purchasing a system that cannot produce reports to Audit's satisfaction. The details of crucial changes to application data are hidden in an encrypted field, and the vendor will not release the decryption algorithm that would allow us to use the details.
So, what to do? Purchasing another system is out of the question. I came up with an audit data collection routine using triggers. However, I worked closely with the vendor engineers, and got them to sign off on the approach. After extensive testing, we all agreed that it did not adversely affect the system's performance.
It is understood that they do not support the routine, and any issues related to the code are my responsibility to correct. But the business now has the reporting required by Audit. Sometimes you just have to make things work in the given environment. But don't do it in a vacuum.
August 24, 2010 at 10:42 am
nick brandwood (8/24/2010)
Steve,I think that long term application development requires a full time FTE and that software houses do not want to employ them because they cost too much for the day-to-day visible benefit they gain. I'm not sure if it really is arrogance, I think it is ignorance. I have always pushed for a full time DBA to manage the database side and have had more no's than yes's. It scares me when we hire developers and they profess to know "SqlServer, Oracle and MySql. Any relational database". To the person hiring this means ok, we don't need the DBA.
Which brings us back to the recurring question: what is a DBA; what does a DBA do? Too often this means doing backups, instance/server/cluster installs, may be running an EXPLAIN every now and again. The value add of someone called DBA lies in building/re-building an application to be "better", which itself means different things to different people; for me it means defining the schema, the (D)RI, the in-database code (if any), finding out whether new tech (SSD these days) is useful. These tasks aren't like coding; there's not much typing and there's a lot of information gathering. IOW, a good DBA doesn't seem to do much. If the application is bought-in, and no-touch, then there's just not much a smart DBA can do.
August 24, 2010 at 11:14 am
I think a common component is coming from this discussion. The safest way to proceed with these kinds of databases from vendors (and MS has plenty of bad database designs too) is through communication with the vendor. Let the vendor know about the issues. Let them know your analysis of the situation and proposed solutions. If the vendor will not listen - you still have options. I had one boss who refused payment until the issues were resolved. The money talked and the issues were resolved.
I don't see an issue with adding an additional schema, procs, indexes,or views. Those don't change the underlying database design for the application. If these kinds of changes are made, one must be very careful about any database updates from the vendor. I have seen some scripts from vendors that drop the objects first and then recreate empty objects. And of course, if they change any tables or columns - your stuff may break too.
Proceed with communication and caution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2010 at 5:43 pm
Be careful! Doing this can often nullify your user suppot agreements with the vendor. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 24, 2010 at 7:19 pm
I remember seeing a 3rd party stored procedure where the developers obviously didn't know the concept of transactions and had written code to throw data into a temp table then row by row in a loop check whether the data had changed then update table and delete from the temporary table to avoid changed data.
The daily task went from an hour to 10 seconds. But we only rolled it out to a few clients that were having specific issues. Our findings were passed on to the vendor, but no code updates in the remaining time I was with that company, probably still hasn't been any.
The support request with them about the initial issue we were having that led to my discovery of the terrible code is probably still open as well.
In that case we supported the vendor's software as our own software integrated with them. We had the change clearly documented, and how to revert back for support or upgrades.
But then I wrote the change and fully understand it.
On the flipside I've dealt with plenty of "dodgy hacks" that have either caused support issues because they behave differently to the norm or even just by simply drawing your attention away from the real issue.
These days my feeling is "do I want the vendor to look better to the business?", which usually is no.
August 26, 2010 at 3:13 pm
I support several vendor databases and I don't make any changes to them. What I do is advise them on what I find. So if I find an index is needed, or needs to be removed, I tell them so and let them know why. Because of that, I have a very good working relationship with the vendors. At one of my previous jobs, the vendor was used to doing their own DBA work and we were one of the few companies that had our own DBA (me). Apparently I was also the only non-vendor DBA who actually reviewed updates/code fixes before running them. It got to the point the company would send me the update/fix first since I had caught errors/issues before.
Vendors don't want us changing things in their databases/code for a valid reason. They roll out one update/fix to all of their clients and expect it to work. If a client has made undocumented changes, the update/fix can fail and then the company has to spend valuable time and effort to figure out the issue. By working with the vendor and letting them know what changes you want made, they can approve it and document the change. I've submitted a change which the company ended up pushing out to all of its clients. It fixed/improved how they were deleting old data.
Some of my suggestions don't get implemented due to internal reasons (the change might cause issues with forthcoming updates/fixes, or other issues), but that's life.
-SQLBill
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply