Voice of the DBA

Author: way0utwest

  • The Basic Security Model in SQL Server – Skill #3

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    Users and Objects

    The basic security model diagram that I use is the one below. It’s not fancy, but it conveys the basics of security in SQL Server.

    security

    From left to right, users or clients are mapped to principals. Those principals are both in the instance (login) and database (user) as well as roles. Permissions are assigned to roles on objects.

    That’s essentially what the basic security model should be for most people. There are other types of structures (credentials, certificates, etc), but in terms of the 80/20 rule, here’s what most DBAs should do:

    • Create a login for a person (either Windows or SQL Server login)
    • Map this login to a user with the same name in those databases that person needs access to. Only pick those databases needed, not all databases.
    • Create a role in each database for each group of users/permissions.
    • Add the users to this role
    • Grant permissions on the objects needed to these roles.

    It’s not complicated, and sticking to this simple scheme, and not granting db_owner or sysadmin to logins or users will allow you to implement basic, easy to understand security in SQL Server.

    References

  • On the Road Again – SQL Saturday #90

    I’m leaving this morning for Oklahoma City for SQL Saturday #90. This is my first time traveling to Oklahoma City and I’m looking forward to it. I’m thinking to check out the National Cowboy Museum, I need to see the national memorial, and of course, get it in a run. It looks like the event isn’t too far from the river, so I might get it in run along there.

    I have a light schedule for the event, which is good for me. I have the opening keynote and then a lunchtime demo session of Red Gate tools. That’s about all I want to do, having learned the hard way that more than 2 sessions is a lot of work. I’m happy to do three if someone drops out, but I’d rather not.

    It should be a good event, and I’m looking forward to seeing OKC for the first time.

  • Create a Log Backup Schedule – #1 Skill You Need

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    You Need Log Backups

    By default databases in SQL Server are created with the Full Recovery Model. That means that without log backups, the log will continue to grow and grow until it hits the growth limit you’ve set, or it fills the disk. I see questions on this constantly at SQLServerCentral from people who have a 10MB database and a 653GB log file.

    A log backup will mark the transactions in the log as backed up, and that space can be re-used. The log backup aids in space management and also provides recovery to points in time in between full backups. With that in mind, you need to do a few things.

    Schedule Log Backups

    As soon as you create a database, and you create a full backup schedule, schedule log backups as well. It’s easy to schedule a single daily log backup along with your full backup. They don’t block each other, and for most non-production systems, this works fine.

    Note that the total space used for the log backups is the same whether you schedule 1 a day or 86,400 a day. There is a slight overhead to each file, but essentially the space used is the same. However backups scheduled more often result in a smaller log files size for the LDF.

  • Keep Data Forever

    Will this keep data forever?

    How long do you expect to keep a piece of data in any of your databases? Chances are if you’re like me, you rarely think about it and expect that you need to store all the data perpetually. As long as you have a job, and the database is being used by some system, you keep the data.

    Over time that becomes a problem. Some companies are starting to examine the legal issues of old data, mostly emails, but potentially other records, and digitally shredding or removing that data over time. For others there are the problems of storage, with the raw space needed to keep all this data growing faster than hardware budgets. More and more companies are looking to archive data that is less used to ease the stress on their storage and backup systems.

    However if you do decide to keep data forever, how do you do it? There are all sorts of issues with various techniques, and I have heard a few companies considering printing out the 1s and 0s and using a more durable storage medium like microfilm or even paper-like substrates that might last more millennia. There is a new type of disk that promises to last longer than ordinary discs thanks to it’s “etching” rather than “burning” process.

    Whether it really works is something we won’t know for years, but I do know that the best solutions for now are migrating your data to new (and larger) storage mediums and crossing your fingers that any archived data on disk, tape, or DVD is still readable if someone asks for it.

    Steve Jones

    The Voice of the DBA Podcasts