Voice of the DBA

Author: way0utwest

  • Targeted Learning

    do not disturb
    Targeted learning means the event isn’t all fun and games.

    I received an email just after I arrived in Seattle for the last SQL in the City 2012 stop. It was from Simon Doubt, who was also coming to Seattle for the PASS Summit, but who hadn’t planned on just attending the conference. Simon had actually come to the Pacific Northwest with a plan: The OLAP Sprint.

    After a few years of attending the Summit and being inspired, taught, and excited by the immersion, it was time to up the ante. The idea was simple, even if the execution would be a challenge. Simon wanted to do more than attend a variety of talks and have interesting conversations. In seven days in Seattle he planned on learning to build an OLAP solution.

    You can read about his journey and it’s an interesting one. It was a success, and while it hasn’t created an OLAP expert, I’m sure that Simon has gained a lot of things from his focused effort. It’s a starting point to learn more. It’s an accomplishment of a fairly difficult task, and it’s a great story that should help a career move forward. This is certainly something I’d put on my resume.

    Throughout the week as I read each entry, I noticed this was a way to tackle the week long conference not as a break from work, but as an exciting new project. The posts showed someone that was taking advantage of the resources at a conference to grow their knowledge and skills. This is a great example of what a plan can do for you, and how you can show your boss that the money spent on this type of training is worthwhile.

    Not everyone will have a plan, and I’d guess most don’t, but the next time you ask for funding think about including a plan of what you are looking to accomplish in a week. Whether you’re attending a conference or an Immersion event, having a plan, with a few specific goals can show that you are using the money you get wisely.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • How Application Roles Work in SQL Server

    One very interesting security technique available in SQL Server is the application role. It’s an interesting way of applying security to a user, and perhaps a way of preventing users from accessing data with unauthorized applications.

    An application role is a role just like any other role. It has a default schema, and it can contain specific rights on securables. Below you can see I have an application role that has rights to a specific table in my database. In this case, at the bottom you can see this table has SELECT rights.

    approle2

    I could grant other rights, but in this case I haven’t.

    How is this different than a normal role? On the “General” page of this dialog, you can see something different.

    approle1

    In a normal (database) role, there are members on this page. Here we just have schemas and a password. This password is what gives you the power of this security feature.

    If I connect as a normal user to my database and issue a SELECT, I get this:

    approle3

    This user has no rights in this database for this table. However if I invoke the application role, I can select from the table.

    approle4

    Application roles are assigned to a user when the user executes sp_setapprole, with the role name and the appropriate password. Previous permissions are removed, and these permissions are granted.

    This can be very handy, as assigning a user rights to connect to an instance, but no rights in a database allows them to connect, but not perform any data manipulation. This means a user connecting from Access, Excel, etc. cannot work with data.

    If the user connects with an application that is configured to execute sp_setapprole after the connection, if there are rights for DML assigned to that application role, the user can perform the data manipulation needed for users.

    It’s not a perfect security mechanism, and it requires the password to be kept secret along with application programming, but it can be a good way to prevent users from working with your data outside of an application.

  • Review Your Indexing

    index cards
    How often do you re-examine your indexes?

    In the latest versions of SQL Server, there are some amazing new features. Many of them allow us to expand the capabilities of SQL Server, but some are added to allow us to dive more deeply into how the system works. A couple of the newer DMVs are fantastic tools to allow us to find indexes that are unused, duplicate, or unneeded. If you’re not using sys.dm_db_index_usage_stats or sys.dm_db_missing_index_details, you should dig into a little and learn how these work. However running a diagnostic query to find unused indexes and then dropping those indexes is a bad idea. You need to ensure that those indexes aren’t rarely, or lightly used.

    I thought about this recently while giving a talk on maintenance. Indexes require routine maintenance, and many of us schedule rebuilds or reorganizes in our databases to ensure that fragmentation doesn’t become an issue. That’s a good start, but there’s more you can do.

    Every month or two you should schedule time to analyze your indexes. Capture a workload from a Trace and analyze it with the Database Tuning Advisor. Take the results and compare them to your current indexing schema. Make a judgement or two on which indexes are used by different queries and spend a few hours testing changes to your systems. You might need new indexes, you might want to remove old indexes that aren’t being used, or you might decide to add a column or include to an existing index.

    There are lots of articles on SQLServerCentral and blogs on indexing that can help you learn more about what changes might improve performance, but ultimately you will really need to test any changes on your own systems. With a little practice, you can build a short routine that allows you to take a few hours every month and analyze a few indexing changes, perform a little testing, and perhaps greatly improve the performance of your applications.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Locking Your Disk

    disk drive
    You should be protecting your disks with encryption.

    This editorial was originally published on Feb 28, 2008. It is being re-run as Steve is on holiday.

    With the tremendous growth in disk sizes and the trend towards more and more people using laptops, someone sent me a note about protecting that data that got me thinking. The SQL Server space has grown tremendously, and not just up. We have SQL Server Express and SQL Server Compact Edition, both of which are designed to run on smaller devices, with (supposedly) smaller data sets. However my phone has more storage than quite a few hard drives I’ve owned in my life and my new laptop rivals the storage in my 3 year old desktop.

    It seems that often that administrators don’t think about the data that gets moved off their servers and with new replication and ETL technologies, a significant amount of data might be duplicated on other instances of SQL Server away from your primary database.

    And your boss might be expecting that you’ve ensured its security.

    So this Friday, I had a poll to see who’s thinking about the issue.

    Do you use disk encryption?

    Or do you think it’s important? Should you be using it for your laptops?

    My laptops really just carry my writings, articles I’m editing, books, etc., and no financial information. I do have Password Safe on them with databases, and I suppose that someone could crack that if given enough time. However since I’m not carrying around data that involves other people, I haven’t worried about it.

    I used to have an encrypted disk when I worked for JD Edwards. At first I thought it was a pain, but after a month or so, it wasn’t a big deal. Except when I had booting issues one morning. Talk about being anxious for a few hours. Fortunately the manufacturer has included utilities and a help desk person was able to get my disk unencrypted and then re-encrypted again.

    Disk encryption is a good idea and it definitely protects accidentally releases of data. If you carry around client data, financial information, or identity information, I’d highly recommend you use some type of encryption to protect the data. However, as this article shows, that might not be enough.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.