Whether your application is an ‘out of the box’ software suite, a Business Intelligence package that can be tweaked on implementation or a hand-crafted bespoke solution if you’re running against a database maintained by someone else or shared with other applications you need to take heed of this point.  Remember that cooperation is key and if you build a good relationship with the DBA and the other key users of the database you’ll have a much better time of things and if there are any critical issues you’ll be included in the remediation process and may even be able to help your own users get back online faster.  It’s easy to see DBAs as grouchy, narrowly focused sorts who tend to view all user activity as bothersome (I can say that as I’ve been one myself) but generally speaking if the DBA is aware of user activity at all the chances are that there’s already a problem as it’s the long running, resource intensive activity that will stand out in alerts and performance reports.  Before your application goes live you should do some testing, run your designs and SQL statements / stored procedures past the DBA for some advice (but remember, you don’t have to take it) and establish some sort of procedure for reporting issues, and remember that an SLA can work both ways as you may need the DBA’s help as much as they might need yours.

Security is a huge issue and as exploit frameworks and toolkits become more and more prevalent and feature-rich the likelihood of vulnerabilities being discovered in our applications should be treated more like a certainty.  If you’re developing bespoke applications and especially web apps you’ll need to pay close attention to the OWASP Top 10 application security risks but from a database perspective the most notable threat is SQL Injection - the art of passing SQL into an application so that it might be executed by the database (as a good starting point check out OWASP’s SQL Injection Prevention Cheat Sheet).  If you’re deploying packaged apps or BI tools don’t think that you’ve gotten away with it, the primary responsibility may be on software developers to avoid exploits but if they’re baked into an application you’re implementing it will affect your users and your business, so…

 

Many Business Intelligence tools include some degree of control over connection management and if you’re developing your own application you’ll have complete control over all database connections, the decision to be made is whether connections are ‘pinned’ open, closed after x minutes or closed at the end of each transaction.  The preference will vary depending on the load and the usage, in most Business Intelligence use cases there tend to be a large number of users, not always connecting concurrently and issuing fairly large queries against the database followed by periods of quiet whilst a report is read – in this case there is usually no need to keep the connection open for long.  On the other hand if you have users issuing a constant stream of small transactions (e.g. a Point of Sale system) the overhead of creating and dropping connections might actually add load to the database so it would be more effective in this scenario to maintain the connection.

This applies more to developers and BI architects where your dogs are your users, if you are deploying an application that creates load on somebody else’s database you should do whatever you can to limit each user’s ability to cause long running queries – in some BI tools you are handed an option to let a query time out after x minutes and perhaps limiting the number of rows returned.  If you are developing your own application you should include both of these options but make sure that you kill the query at the database level rather than just killing the thread in your application that made the request otherwise it’s equally bad if not worse since the user may simply re-issue the offending query.  The actual limits are bound to vary from database to database but that’s where the first point comes in, discuss this with both your users and the DBA.

When it comes to solving problems try to stick within the basic and simple boundaries of an ordinary user, avoid using undocumented stored procedures, excessive use of user defined functions, custom data types, plugins and extended stored procedures or anything else that strays too far from a standard install of the database platform.  Obviously you’ve got an app to deploy and you want to solve your problems in whatever way is best for your users but the further you are from a standard deployment the more issues you’re likely to encounter, both you and the DBA might be fully aware of this amazing new setting you tweaked to make things run better but a couple of years down the line during a disaster recovery will it all come flooding back quite as easily?  What if one or both of you that setup the application have moved on to other roles?  Thinking outside the box is great but be conscious of introducing risk and if you do feel that it is necessary then make sure that it’s well documented in the Run Book or the corporate wiki.

Since you may already have elevated privileges on your own database, a shared database or even the server you may be tempted from time to time to perform maintenance tasks or make minor ‘improvements’ to indexes or configuration settings – do not do so without the DBA’s blessing.  If you’re following the rules above you’ll probably have a fairly good rapport with the DBA already so it’s likely that you’ll be granted some level of trust not to mess things up but be careful not to overreach, the DBA will be ‘in the loop’ of many changes and other requirements (e.g. critical deadlines, disaster recovery tests, unplanned maintenance) whereas you may not be aware of them so before you make any changes run them past the DBA – just in case.

If you’ve ever been a DBA you’ll have seen, on more than one occasion, tables popping up called tmpSomethingorOther, tblToBeDeleted or TableName_bak but when it comes to the key questions (How long have these been around?  Are they still required?) nobody seems to have a straight answer.  I know myself that whilst I’ve been developing data warehouses I’ve created these sorts of tables and subsequently forgotten what they were used for, not too much of a problem if you’re ‘the guy’ but in a large team or with personnel changes over time it can be hard to know what is required and what isn’t – I came to a database once with temporary tables over five years old which had not been deleted out of fear that they were important.  The moral here is an obvious one, clean up after yourself or if the table must exist for some short period of time put a note in your diary to come back and cull it.

Be mindful of what errors you raise and what you write to public logs, if your application causes a large amount of data to be written to database or other centrally collated logs you may inadvertently make it harder to detect genuine issues which will hurt both you and and other users of the database.  If you do occasionally need exhaustive logs consider adding a ‘debug mode’ into your application which can be turned on or off via a configuration setting, that way you can turn it on whilst you’re tracing a fault and need more verbose logging then turn it off when you’re done.

There can be plenty of unexpected hazards on country roads so don’t always rush around everywhere at 60mph, acknowledge that whist you might want everything to go as fast as possible you could be causing some other critical process to slow or stop.  Driving at night can be treacherous too as you might come across an unexpected backup window or import/export process, talk to your DBA and coordinate the major tasks.  If it’s a shared server make sure you have access to the task list so that you know where to slot in your jobs and that those jobs get put back into the master list.

Really it comes down to one thing, as the great and wise Jerry Springer oft said, “take care of yourselves, and each other”.