Voice of the DBA

Category: Editorial

  • Porting SQLServerCentral

    Like a few of you, I’ve been working with WordPress as a blogging platform. Over the years I’ve tried a few different pieces of blogging software, but I really like WordPress. I’m not alone as there are estimates that 20-30% of all websites run on this platform, including a few you might not expect. I thought UpperCup and Krispy Kreme UK are sites that don’t really look like they’re powered by WordPress. Those make my own blog and T-SQL Tuesday look pretty bland. Maybe I’ll do a little design work at some point on those. That’s after SQLServerCentral moves over to the platform.

    SQLServerCentral started as a custom ASP site many years ago, then upgraded to ASP.NET at some point. This was a joint effort from the founders to build in new functionality and features as we needed them, purchasing components (like the forums) where we found a suitable product. This first evolution of the site lasted for many years until Redgate Software acquired the property. We then underwent a second platform shift to NHibernate, which has been underpinning the site for a decade. We now move forward with our third evolution.

    We have a project underway that is porting our site to WordPress, for a variety of reasons. Like many of you, I struggle to get resources assigned from my employer for the projects that I’m passionate about if they don’t rise in importance above other things being worked on. There are only so many resources available, and they must be shared by the company. While Redgate values SQLServerCentral, we have a site that works well, and has worked well for many years. Thus, it’s not the same priority as some of the other projects in the company. Since we have some requirements around better mobile support thanks to Google, we had to move in some direction.

    We have struggled with skillsets over the years as most of our web developers aren’t well versed in NHibernate as we’ve moved many of our other web projects to WordPress or more basic technologies like React. Building all the various features from scratch would be a big project, not to mention a constant maintenance headache, so after reviewing some responses to our RFP, we decided to go with WordPress, under Project Nami. This is an open source project that replaces MySQL with SQL Server. While I run MySQL on T-SQL Tuesday, one of our key requirements was that we use SQL Server as a database, and Project Nami allows us to do this. Since there are numerous people with WordPress skills, and lots of plugins that can be easily added (or removed), our view is that WordPress will allow us to grow and change the site over time with fewer resource constraints.

    The last few months have been a long, drawn out project as we needed a number of custom plugins written, or existing ones adapter for some of the functions on the site. At its heart, SQLServerCentral is a rather unique publishing platform, and we needed to preserve much of this functionality. As with most projects, we’ve run over time and budget a bit, but we’re now getting close. I don’t have a date yet, but I anticipate we’ll add more user testing in January and then make a switch sometime later in the month.

    I hope that you’ll find the new platform to be very similar to what we have now. Our goal was to change relatively little in terms of functionality and minimize the look and feel changes. There are some, but I don’t think they are too disruptive. However, we will be looking for feedback and make decisions on what things we’d like to change or adapt for the future. Keep an eye out for more announcements and fingers crossed that everything goes smoothly during the deployment.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Better Static Code Analysis and Security Scans

    I was listening to a talk from Stefan Simenon on their CI/CD transformation within ABN AMRO, a large financial company. One of the interesting things he noted was that they consider open source to be less secure, possibly with more vulnerabilities than in house written software. Their build pipeline will fail if a developer starts using new OSS components.

    I find that interesting, as the DORA 2018 State of DevOps report sees more use of OSS software in companies that are adopting DevOps. In general, I think that having many people able to view the source and find errors makes companies feel that open source is more secure. I think that’s likely more true, though it’s a bit of a philosophical argument. We can look at some data, but it’s hard to prove that one or the other is empirically more secure.

    The thing I agree with is that using new components without some review is not a good idea. Whether this is written in-house, copied from an Open Source project, or purchased from a vendor, we need to perform some testing and analysis of the code or component.

    This is also true in database code. When we get a query from a developer, it’s often easy to determine what is happening, but when the size of code grows, or there is a large stored procedure, we often don’t perform a detailed analysis. What’s worse, we don’t have good static code analysis tools for database languages. As much as I like what Redgate Software has done with SQL Prompt, I know this is rudimentary and is built to avoid code smells. There isn’t any detailed look at whether the code is secure, or if there might be unintended effects.

    There aren’t really any good tools I’ve seen, though I’m not even sure what I’d want here. How can a tool tell me that querying 4 tables and updating 3 more is OK, but an insert to some other table in a separate database is bad. That insert to the other database might be what a malicious actor wants to copy data elsewhere. The best thing to me would be some analysis of what objects are being touched and how, which could help alert developers to potential issues.

    Building static code analysis tools for database languages is hard, but it’s something that our industry needs to do. This is even more true when we start to have more programmability features, like the ability to execute other languages inside of our database engines. In those cases, not only do we need to ensure the code for another language passes test, but that we understand what types of interactions our database code has with those modules.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Evolving Our Tools

    This week the next preview version of SSMS v18 was released. This is the sixth preview release, and I’m guessing that this will be one of the last. Six seems like a lot of releases, and I’d like to think that this is getting close to being ready to use by most people, but I’m not sure. I certainly have some some annoying and problematic bugs, so I can’t be sure there won’t be a seventh, eighth, or ninth preview, but it does seem to be fewer issues are being reported.

    With the release of SQL Server 2016, SSMS was decoupled from the database engine, and we saw some SSMS v16 releases. I didn’t use many of those before moving to SSMS v17, which came slightly after SQL Server 2017. I’m glad we’ll start to get the versions separate from the engine as this is confusing to many people. However, I do expect that plenty of people will call this SSMS 2019 or think there’s a SQL Server 2018, etc. If we could get more people to leave the older SSMS versions that came with 2008 R2, 2012, 2014, then I’ll take a little confusion in how we talk about SSMS.

    However, we don’t need to stick with SSMS these days. If you’ve been heads down and just focused on keeping your existing systems running, you might not realize that not only do we have different SSMS choices, but we also have other tools. I’m not talking about SQLCMD, bcp, and Visual Studio, but we have other ways of working with SQL Server. Visual Studio Code has an mssql extension if you write code in that IDE, which might be something you full stack developers need.

    For the SQL Server people, we have a fork of VS Code in Azure Data Studio. This is a lighterweight IDE built for SQL Server work. We also have the mssql-cli tool, giving us way more control over command line work than we have with SQLCMD. I haven’t worked with it much, but it’s on my list for January to play with a bit. I don’t know how well either of these will catch on, but let me know your thoughts. Are you doing more work with either of these tools?

    There are certainly plenty of other choices as well. My company (Redgate) and others make plugins for SSMS that improve how you work with SQL Server. There are even other IDEs, such as DataGrip, that you can use and abandon the Microsoft tools altogether. I’m not sure I would look to leave SSMS entirely, but perhaps I should give some of these a try at some point.

    Tools matter to many professionals. Mechanics treasure their sets of wrenches, chefs love their knives, and we ought to have tools that we know, use, and are comfortable with. This includes both the actual software and the various scripts, code, and helper applications that allow us to work efficiently. If you don’t love your tools, or have a collection, maybe now is the time to start the new year building some skills with the one you use, or try a new one. Wayne Sheffield has a nice series on SSMS and I’m hoping to get some other pieces written for other tools. If you want to tackle one for SQLServerCentral, let me know.

    If you’re looking for a new tool to try for SQL Server work, might I suggest some PoSh and dbatools. It’s an amazing combination for lots of tasks.

    Steve Jones

  • The Full Stack DBA

    The last couple of years have seen the job title “full stack developer” posted quite often. With the popularity of DevOps, companies are hoping to hire a developer that can work on the database, understand some networking, and continue to build front end applications. In my career, I’ve done all of this while being just titled a programmer, but apparently with more specialization these days, some want to differentiate themselves as being able to do it all.

    I saw this tweet from Erin Stellato, asking if people manage multiple items. Specifically, she asks about “the virtualization, the server admin, the storage, and system security”. No networking, firewalls, etc. in there, but I’ll add those in as well. This week, can you answer this question:

    Are there any of you full-stack DBAs? 

    By that I mean you work on more than the database. Do you manage storage at all? Are you a domain admin with AD responsibilities? Do you manage applications or perform some development? Is there any networking in your daily work?

    I’ve actually done all of these before. At one startup, my primary responsibility was a database developer and administrator, but I had AD rights and needed to help our sysadmin with Exchange and various other servers. I setup and configured our T-1 line to the co-location facility, and explained how to get two NICs in our SQL Server working so that backups and office access didn’t interfere with application access from our web site. I even had to help build new network cables one day.

    In a few jobs, I’ve actually been a domain admin, even though I was primarily a DBA. That was because we shared responsibilities and on-call on the team. Even though my primary work was with databases, I ended up helping work on other systems at times. To me, these were welcome distractions and also chances to build knowledge that was useful in troubleshooting and narrowing down the scope of problems.

    Are any of you still doing that today? Whether you are or not, let us know what types of responsibilities you have at work.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.