Voice of the DBA

Author: way0utwest

  • Database Mirroring Needs FQDNs

    A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

    I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

    I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

    Then I ran this on the primary:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'

    After a few minutes, I got an error:

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://192.168.1.201:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'

    I knew I had a problem when this took more than 5sec to respond. Again, an errror.

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://SQL02:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'

    I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

    Hopefully this blog will help me remember.

  • The 43rd Birthday for SQL

    I ran across a neat post that reminded me that SQL is 43 this year. It first appeared in 1974, and not only is it still in use by many of us on a daily basis, it’s the second most common language in the Stack Overflow developer survey. Only Javascript is more commonly used, which is also somewhat amazing. Maybe Javascript really is taking over the world.

    I’m not surprised that SQL is so heavily used (half the developers report it’s one of their languages), since almost every application needs to query data from a database. Whether you use a relational store or not, SQL is likely your language of choice. Many NoSQL database technologies have bolted some SQL query capabilities onto their systems precisely because SQL is handy and helpful in sifting through lots of data.

    I think SQL is a mess of a language in many ways. The DDL is poorly crafted, and even the DML reads funny to me. The order of operations is nothing like the structure of the query. I’d expect more of a LINQ like syntax (where is the data coming from first) that logically flows the query from data to result. I’m sure that will not change anytime soon since we’re so used to the SQL language. Even those developers that like to write in LINQ really need to know SQL in order to check their queries against back end systems. The survey of loved languages backs up the idea that SQL isn’t great it falls far behind many other languages. However, many of those are more modern languages that provide more convenience and help. SQL is fairly bare bones, with rather immature tooling for a 43 year old.

    SQL Server is also doing well, showing up in almost a third of developers’ work, ahead of PostgreSQL, Oracle, and beaten out by MySQL. That’s a testament to the power, ease, and feature rich nature of SQL Server. I wonder if the release of SQL Server on Linux will grow these numbers. My thought is that those companies using another platform on Linux might consider SQL Server instead, perhaps because the robust nature of the platform along with the ease of development may get more people to try it. We’ll have to check back in a year.

    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.

  • A Database Design Test

    I was watching a presentation recently on database design and the speaker talked about how he hires developers. These are full stack developers, for whom the database is a portion of their responsibility. One of the tasks he gives them is a short database design test, designed to get a rough idea of their knowledge of databases.

    The test is a short story, with a classroom/course/scheduling scenario. There are descriptions in some business terms, and the instructions ask the reader to to decide how to put entities together and link them. There is a sample unlabeled diagram with only a couple boxes. The diagram is meant to clue them in to the way to indicate relationships, and there are names of different structures in the test in bold. For someone experienced in databases, this would seem trivial as the entities are listed in bold, and the test is designed to be completed in 5 minutes. Extra points for not crossing any relationship lines.

    I found this to be a nice, short test to gauge a developer’s knowledge. The speaker noted that they didn’t worry too much about time taken, or the exact notation used in the digram. This is mostly a way to measure if an individual thinks in terms of entities and connections. This is part of a few tests used for a basic evaluation of how a developer solves practical problems, and avoids the trivia based examination used by many interviewers.

    I was intrigued since I’ve never been really asked to design anything and I’ve had quite a few jobs where that would be a portion of my job duties. No one has given me a scenario and asked me to produce an ER diagram. The most I’ve gotten is some theoretical questions on normalization, or what keys are. I wonder if I’m alone. For those of you reading this, have you ever had a design test of any kind in an interview? Can you disclose the types of questions or scenarios? When were you tested? It would be interesting to see if this is used by much of anyone.

    There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients. I suspect that few people understand them, or even write them, even though they can be invaluable when trying to write reports and understand the relationships between different entities. Many ask, but could those people produce one? Or read it beyond realizing which field in table a connects to which field in table b? Let me know this week if you’ve been tested on your database design skills.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Handling Data Corruption

    I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

    Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don’t know how many backups are valid.

    What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn’t known immediately, and maybe you’ll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

    The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

    We always want to be prepared, but we won’t have actual preparation plans (scripts, documents, etc) for all situations. It’s much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today’s question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

    Steve Jones

    The Voice of the DBA Podcast

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