I work for an insurance company. Five to six years ago the extent of our reporting was printing off and bursting AS/400 spool files and mailing them to our agents. We have one tech guy who wrote perl scripts that would render these spool files as html pages. It was a very crude way for our agents to get their data, and the data they did receive did not contain a lot of information.
One of my first projects here was to implement a new reporting system for our agents. After some research we settled on Crystal Reports and Business Objects, with a Datamart storing the agent data. I built the Datamart on an instance of SQL Server 2000. Our agents loved this new reporting system, and as such the requests for more reports grew. Marketing was in charge of determining what reports were necessary, and a few over-eager marketing analysts started creating databases and DTS packages of their own.
Although not my role at the time, I took it upon myself to stop this mish-mash of unorganized data collection and storage. I convinced the company to upgrade to SQL Server 2005 and house it on a dedicated server. Gathering my knowledge from articles and blogs, I built a Database system according to best practices (different filegroups for system tables and data, different drives for logs, backup plans, etc). I also consolidated all the table refreshes into one SSIS package, cutting the load time by 75%. I also clamped down on security, so others couldn’t just create things willy nilly. Both Marketing and our agents were happy.
Unfortunately, we still have a problem within our IT department. Our IT department has several different applications that all require databases. Our company still hasn’t really embraced the concept of a dedicated DBA, even though that is my title. Whenever IT installs a new piece of software that uses a database, they just perform a standard install, which invariably creates a new database. IT pays no heed to where this database is stored, or of its makeup (can you say “just click ‘Next'”?), so I am constantly reconfiguring and moving these databases to conform to the specifications I’ve set up. There are times where I am not even aware that IT has created a new database, and I find out only when I’m asked to create a backup plan for it.
I am slowly changing that mentality, and really hope to hammer it home when our actuarial department starts using a new data warehouse next year. Whenever Marketing has a change they want to implement to a database or an SSIS process, they know to clear it with me. Actuarial is on board as well. I only hope I can convince the IT department to follow suit, otherwise we’ll continue to have SQL Server instances and databases strewn all over the place.