Voice of the DBA

Tag: sql server

  • Debugging SQL Server

    One of the tools that I found useful early in my development career was the debugger. Being able to track the values of variables, check the call stack, and pause execution of programs was handy. Early in my career, the tools were very rudimentary, but the latest debuggers in Visual Studio are quite advanced. I remember using a great debugger in Rapid/SQL years ago that helped me with some SQL Server 2000 code.

    There are debugging tools included with SQL Server, but the last time I used them, they seemed to be a bit flaky. However the need to follow your code slowly along it’s execution plan hasn’t changed. I’m curious this week, what many of you do inside of SQL Server to debug your code. I wanted to ask you this week:

    How do you debug your applications that work with SQL Server?

    These could be .NET applications that query the database. You could have ETL processes using SSIS or some other tool that you work on. Perhaps you have a system that runs entirely inside SQL Server and you need to untangle your T-SQL.

    Do you use Visual Studio tools? Have you configured the T-SQL debugger? Are you a PRINT statement or temp-table-for-results developer? Perhaps you have logging or some other mechanism that you use?

    Let us know this week what works well for you, and if you’ve found a particular technique to be handy in a situation, we’d love an article that might teach someone else how to debug their code.

    Steve Jones

     

    The Voice of the DBA Podcast

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

  • Hadoop and SQL Server

    There has been a lot of media attention to Hadoop in the last few years. In fact, Microsoft has spent a lot of resources to build the HDInsight version of the platform and integrate it into SQL Server. I’ve read quite a bit about how to setup and query with Hadoop, but haven’t used it for a real project. In fact, it seems relatively few people seem to be finding it to be a replacement for, or better solution than, SQL Server. We published a great introduction to Hadoop written by David Poole awhile back, and recently I ran across another nice writeup from someone I think is a very talented SQL Server professional.

    Michelle Ufford (@sqlfool | b) wrote a piece asking if Hadoop is better than SQL Server. Michelle notes that Haddop is a different platform, and it’s a great way to consume lots of data. In fact, she has a graph from EMC talking about the data explosion and how we still at the low end of the exponential growth curve of data production. It’s a sobering thought and I tend to agree with Michelle and EMC on the growth of data.

    I had hoped Microsoft would do more with Filestream and Filetable to help meet the challenges of large volumes of data, but it seems that very little has been done with those features in the last version of SQL Server. I have little hope that additional investment will come in the future. Instead, it seems Microsoft is leaning towards using Hadoop as one way to process and consume large volumes of data.

    I wrote about Hadoop in 2009 when it was a young project, and I suspected it would enhance and work with, rather than supplant, the RDBMS. There are certainly other technologies out there to help with this, but if you are working with large volumes of data that exceed what a single instance of SQL Server can handle (at a reasonable cost), you might think about learning a bit about Hadoop. It might not solve your issues, but if it can, it would be good to know something about it.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Multiple Backup Files

    I’ve been writing a little PowerShell lately that will back up databases, move files, and restore them. I’m often testing things, and having scripts to quickly and easily move files is very handy. After one of my posts recently, a reader asked if I’d considered multiple files and how to handle them in scripts. I confessed I hadn’t, mostly because I haven’t had to deal with them.

    In my career, I’ve tended to work with small to medium sized databases. I’ve had young children for a large portion of my SQL Server career and had no desire to babysit multi-hour (or multi-day) restores when things break. I know some people have been through those situations, and good for them. I just know I’ve had enough issues with the low-GB sized database, and haven’t been interested in supporting TB sized systems.

    However that likely wouldn’t be the case in the future. More and more companies are collecting and storing data that reaches into the TB, even in small companies. The rapid advances in sensors, development tools, and cheap storage means that many people are dealing with hundreds of GB in at least one of their databases. That means for a reasonable RTO, making quick backups, and maintaining good performance, multiple backup files are becoming a necessity.

    Is that really the case? Data volumes are exploding, but you many of you using this feature? I wanted to see how many people have implemented, or at least thought of striped backups. The poll this week is:

    Do you have any databases that benefit from backup to multiple files?

    I’ve consulted with clients that accidentally produced striped backups and then lost one of the files. That’s never a good situation, and it’s bad news to have to give as a consultant. However, I’m sure many of you have consciously implemented striped backups because they can perform better than single file backups and make for quicker restores. Others of you may suspect (or have tested) striped backups will help your systems but haven’t gotten around to setting things up.

    Let us know if this is a common feature you use, or is it still something esoteric that you have no need for. And if you have put striped backups in place, have you tested a striped restore? I certainly hope so. Let us know either way.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Building Connection Strings

    I ran across a great article on Connection Strings from my good friend, Allen White (b, t, c), recently. Quite a few of the items he mentioned in there, I knew, but it was a good review to stop and think about how to actually connect to systems. Especially as many of us will be moving to more complex, distributed environments that might include cloud (public or private) resources.

    The Address

    The address part of the connection is simple for many people, but often misunderstood if you’ve only worked in single domain with default instances. Many people are used to just putting in the name of the server and connecting from SSMS or some other dialog based application.

    However I do think it’s important to understand that connections to SQL Server are just like browsing the web. For example, I can do this:

    tcp:xxyyee.database.windows.net, 55345;

    This is a connection of the format:

    protocol:name of server, port;

    We don’t often worry about protocols, but in case you might want to ensure it’s a TCP connection, specify it. This is like:

    http://www.amazon.com

    In this case, I’d specified http. However you should try ftp:, https:, or other protocols in your browser. You might be surprised how often these work.

    The server address is a fully qualified domain name (FQDN). In a single domain, local network, you often just put the name of the host, but you should be aware that any address works.

    The port is 1433 by default, but you should be aware that this can be changed by the SQL Server administrator in the Configuration Manager, in Services, or even with firewall rules. If you’re ever in doubt, specify it.

    Security

    I hadn’t thought about security being more than trusted or not, but Allen pointed out a couple of settings I need to use. He talks about using SSPI to ensure Kerberos is used and also to set a value to encrypt the connection string. Both are good ideas.

    Five Minutes of Learning

    Read Allen’s article and learn a bit more about connection strings, or review your knowledge. Little pieces like this can help keep your skills sharp and allow you to pick up tidbits of knowledge that might really help you some day.