Voice of the DBA

Tag: syndicated

  • 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.

  • Come Shake My Hand at PASS

    Last year Andy Warren thought about trying to help first timer attendees at the PASS Summit with a little networking event. Two years ago he brought Don Gabor in for a networking pre-con that was very popular, and wanted to try something new. So we rented Lowell’s out down at Pikes Place Market and invited people down. We weren’t sure how it would turn out, but we decided to risk some of our money and see.

    It was a smashing success, with dozens, maybe even a hundred people there. I got to meet quite a few people for the first time, which always makes me smile. I am sure the same thing happened to many other people and the networking was great.

    We decided to do it again, and we’ve reserved Lowell’s again from 6pm-9pm on Monday, October 10, 2011. Anyone is welcome, and it’s a short walk from the convention center down to the famous Pikes Place Market.

    Register today if you’re coming

    We ask that you register so we can plan the staff with the restaurant. It doesn’t cost anything, but you are responsible for your own food and beverage bill.

    Come say hi, meet some new people, and shake my hand. I’d be happy to meet you.

    We are sponsoring this as part of The Mentoring Experiment.