Saving Time with F# Type Providers

One of the ongoing projects my team has been working on at the office is an effort to get A records created for all of the databases on our internal servers to make maintenance easier. Once every DB has a DNS name associated with it and the configurations for all of the applications that use them have been updated to use those in their connection strings, migrating databases from one server to another will be a non-event. Theoretically at least. Instead of finding every occurrence of a database and updating the connection string in every config or other or database or wherever else from server A to server B for a single application, then rinsing and repeating for everything that touches it, a world with a DNS name for each database is a world where all of those connections can be fixed with a single tweak to an A record and an ipconfig /dnsflush.

There are a lot of servers in our ecosystem and each of those typically has more than two dozen databases. There are some exceptions that hold only a handful, for reasons ranging from security policies to performance needs and disk space considerations, but the last official count I got of DNS names we will eventually need was in the neighborhood of “a lot”. In the biggest round we’ve created at one time so far, there were something like 160 databases that needed names. The names themselves are uninteresting once the convention is established, a list filled with {{Database Name}}DB.{{domain}}. Each DB typically lives in a development, acceptance, and production environment so we create three A records for each DB. Developers don’t have access to make those kinds of changes to the environment in my world so we put together a list of what we needed, fired off a change request, and waited. If we had any desire to spot check the list once the work was finished and before releasing it to the rest of the organization to start updating the related applications we had something of an uphill climb ahead of us. 160 X 3 = much closer to 500 connections than I’d like to test by hand.

That of course raises the obvious question, so I’ll address it in hopes of saving everyone some sleepless nights: Yes, we have interns, but I actually like them.

When I started my first “real” job in the field shortly after finishing college, I remember a coworker saying something to the effect of “good programmers are somewhat lazy by nature. They’ll spend hours and days and sleepless nights on the interesting problems because they think it’s fun, but give them a simple boring task that needs to be repeated a lot of times and they’ll drop everything in search of a way to automate it to make sure they’ll never have to do it again.” It was in that spirit that I found myself too lazy to verify even 10% of the entries by hand. Firing up SQL Management Studio, connecting to each server with the correct credentials or my Active Directory account, and confirming that the first entry in the list connects to the database that the DNS name implies, move to the next server, disconnect, move to the next item in the list…ugh! I briefly considered copying the list of DNS names into a batch script that pings them, but a) that wouldn’t confirm the existence of a SQL Server installation on the machine, just that it existed, b) even if we could assume that the servers were all SQL boxes it wouldn’t confirm that the correct database was present, and c) not all of the servers on the network are even pingable. A second after discarding that I considered throwing together a C# console application to connect to each one in turn and fire off a USE {{DATABASE}} command to check that the expected DB shows up on the server, but before I could consciously talk myself out of that I realized that the perfect answer involved F# type providers.

Because type providers give compile-time checking that ensures a there are no surprises by the time an application runs we know that any code we write that builds properly is going to work at runtime, at least with respect to the database. In this case there wasn’t a particular object that we needed to touch in each one; we simply needed to confirm that the DNS names were pointing to servers that held the DBs we expected. In hopes of a quick gamble paying off, I threw together a SELECT statement against the table where we had stored all of our database information and used it to generate a type provider declaration for each of the DNS entries we wanted to test. Fortunately all of our developer Active Directory accounts at least have read access to all of the SQL Servers, so all of the declarations take a form similar to the code snippets below.

F# type providers generated from a T-SQL query

After all of the connections were generated, I copied and pasted them in to the F# project and built it. There was no need to include additional code that would actually use the types for anything because the type providers will check that both the server and database are accessible at compile time, so the only other code is the main method that comes with a new console application.

F# console application default main method

I received handful of error messages because the servers were running versions of SQL before 2012 and the type provider was incompatible with those earlier versions. That gave us a list of names that we could guess were correct because we knew those DBs were older and likely to live on older SQL boxes but we needed to exercise a little due diligence to confirm our suspicions. The rest of the errors indicated that either the DNS entries were wrong or simply didn’t exist as expected. In less time than it took to explain what I had done to my coworkers who were unfamiliar with F#, I had tested every single connection and found the subset of entries that needed more attention. The crowning achievement was of course when the most anti-functional developer on the team admitted that it was a “pretty neat approach” to a seriously tedious task.

From here, my next step is just to convince everyone in my ecosystem that we should all be writing F# all the time for basically everything. Timeline TBD.

Tagged with: , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *