Voice of the DBA

Category: Blog

  • SQL Server Thumbnail Metrics – OS Memory

    I’m gathering a few metrics around the Internet for SQL Server from people that I think really know how to run a SQL Server. This is a series of posts that I’m making to gather these metrics up for easy access later.

    Host Memory

    The short metric is:

    Leave 4GB for the OS, or 10% of physical (or guest) memory, whichever is greater.

    Examples: If I have the following amounts of RAM, I leave this for the OS.

    • 16GB of RAM – leave 4GB since 4 > (16 * 10%)
    • 32GB – leave 4GB since 4 > (32 * 10%)
    • 64GB – leave 6.4GB since (64 * 10%) > 4

    Essentially leave 4GB until you get to 48GB of RAM, then go to 10%.

    References: Mr. Brent Ozar of Brent Ozar, PLF who’s presented and written this a few times, but the reference I’m using is: How to Set SQL Server Max Memory for VMware

    Explanation: In the older x86 days there were all sorts of rules for setting memory for the OS and SQL Server. I won’t go into those, and for the time being we’re working in the x64 world, not x128. For that, you need to be sure the OS isn’t starved by SQL Server and unable to manage the host processes. These days 32GB isn’t expensive, and 4GB gives you a nice cushion at lower levels. At higher ones, you need

  • The Last DBCC CHECKDB Date and Restores

    I ran across a question on Twitter recently where someone asked about the DBCC CHECKDB date after a restore. For those of you that don’t know this, you can run this command on your instance (in a database)

    DBCC DBINFO WITH tableresults;

    This returns a lot of information, but I’ve circled one value below:

    dbcc1

    This is the last known good DBCC date that exists for this database. It’s also the value in the error log from the execution of DBCC CHECKDB on my instance early this morning (Arrow marks the entry).

    dbcc2

    If I were to restore this database, what happens to this value? I’d expect that it would be restored to the last value that was contained in the backup file. That would make sense to me, but let’s test it.

    First I run a backup:

    BACKUP DATABASE [db1] TO  DISK = N'C:\SQLBackup\db1_20121115.bak' WITH NOFORMAT, NOINIT,  NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    This should have the last DBCC date inside the file. I know run a CHECKDB and note the time in the error log.

    dbcc3

    This returns successfully (whew, didn’t want to test corruption restores). If I then run DBINFO again, I get the current date from the error log returned. Now let’s restore from my backup.

    This completes and when I run DBCC DBINFO again I find the dbccLastKnownGood date is reset back to the 12:04am value instead of the 9:11am value.

    That’s what I expect, and that was what a few other people confirmed on Twitter. It’s logical that this should be the behavior, but you never know until you’ve tested it.

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

  • Refreshing

    DadDelaney_ski_Mar2012I hope everyone has a great week this week. I’m off, trying to recharge and refresh after a rough speaking schedule this fall.

    My family has a tradition of spending Thanksgiving up in the mountains skiing. There isn’t a lot of snow, but I look forward to skiing, dominos, ping pong, and more with my wife and kids.

    You should be sure that you take a break periodically from work and make sure you refresh and recharge. All too often I see people skipping vacation, which isn’t good your mental health.