Working with Local Government means we have a lot of business areas and not a lot of staff to support it. So how do you handle this COTS application? The trend for this currently is to make the cloud-based. This is not entirely a bad thing. It’s more like a necessity to keep the costs down. These specialized systems cost hundreds of thousands, and each interface is 20-50k also. But the focus of this blog is more about the methods of getting data back and reportable.
There are basically four methods of getting to the data you spend all your days inputting.
- Direct access -This is the easiest for you and your agency. Yes, it will come with latency, but that should be tolerable. But the vendor then must pay for your access. Why is this an issue? If you have a system like our jail system, you have 300 reports run by the sheriff’s department, courts, community corrections, and community mental health. The same report is running 50 times a day. And the vendor is now paying for the transfer, for the math-challenged that 15,000 times and the cost is no longer controllable.
- Database backup – The second easiest, and it solves the controllable cost for your partner vendor. But it needs to be in a format that works with the rest of your environment. One of our systems is a MariaDB, and database backup is MySQL, and we use a more common database SQL Server. For this, I take the backup from MySQL and convert the script extract into t-SQL code. This involves a set of replaces that looks like:
replace LOCK TABLES [ with –LOCK TABLES [
replace unlock TABLES; with — UNLOCK TABLES;
replace dbo.tinyint with tinyint
and about 40 more and a few I was not as easily scripted like enum….
In all, I like this method. And, I would like to see this over the next style of API. I have used a tool to reduce this down called SQLines, and they have an online version that works great for small scripts. Mine was huge, so I needed to have the paid version at a whomping $200.00.
- API – This is not a bag choice if you have a team to work on the coding and are able to deal with different styles of API implementation styles. Generally speaking, this means that it would be me tweaking the code from this time to time if you did not spend enough time making the automation smooth.
- Custom interface – This is great and by far the easiest, but the technology changed quicker than our RFP process. Most of the systems do not have this defined and require you to use one of the first three methods. But that is why I am writing about this.
Update your RFP template to handle defining a process to extract data for your agency’s use. As a Local Government DBA, self-proclaimed CDO, I have added to the RFP sections for On-premise and cloud.
On-Premise – I have areas defined for the database server and database server OS (Windows SQL server works a lot better than Linux does. I also define our reporting tools for SSRS, Power-BI, A database entity relationship diagram requirement, and most importantly, Sysadm access to the database. Sadly, prior to this being added, a few snuck in without these details.
Cloud – Exit strategy, what happens when we are ending our contract to get our data out. That we are the owner of the data, that we get regular extracts of data in my favorite format to make it easiest for the team to use the data in a database, interface, and/or a data warehouse. What and who has access to the database, what type of data sensitivity do they have (HIPPA, CJIS….) What cloud provider is using, and does it use fed-RAMP standards. Then I talk about the maintenance and maintenance windows. Of course, we must talk about RPO/RTO. These items are to fold, how we want to get to the data and how it is protected to keep us working.
The RFP section also has the third part. The common stuff.
This is something you, as a Local Government DBA/CTO, must add and stay on top of on your RFP process. Have you modernized your RFP process yet? If not, do you want help making this easier and more workable for current technology? I am here to help make RFP deliver a better solution to your organization. Let’s get working together.
