Copy-Only Backups

Tibor Karaszi talks about copy-only backups:

If you specify COPY_ONLY for a full backup, it will not affect the following differential backups. I.e., the following differential backups will be based on  the last full backup which was not performed with COPY_ONLY. Another way of looking at this is that a full backup using COPY_ONLY will not reset the DIFF (also known as DCM) page, which is page 6 and repeated approximately every 4 GB in each database file.

If you specify COPY_ONLY for a log backup, it will not affect the following log backups. I.e., the log backup chain will be without the one(s) produced using COPY_ONLY. Another way of looking at this is that a log backup using COPY_ONLY does not empty (truncate) the log.

That’s it! COPY_ONLY for a full backup does not, in any way, affect the restoreability for log backups.

The copy-only is a great feature, but understand what it does and how it works.

DR On The Cheap

Derik Hammer’s final Availability Group architecture post covers disaster recovery on the cheap:

This architecture can be used when your organization does not value their secondary data center the same as the primary. It is a best practice to have matched or similar hardware between your primary and disaster recovery sites but that is not always possible. When costs need to be reduced it is better to have one failover server that you know can handle the work load rather than two servers which are under powered. Under powered hardware can easily become an effective outage if they cause timeouts as soon as a production work load is placed on them.

For many small to medium-sized enterprises, this might be the easiest route to sell to management—it’s hard to get management to have “redundant” servers which normally don’t get used.

Capturing SQL Server Perfmon Counters

Andy Galbraith shows how to collect and store Perfmon counters:

As you can see, Page Life Expectancy (PLE) on this graph dips, gradually climbs, and then dips again.  With a collection every five minutes you may not catch the exact peak – all you know is that the PLE was 50,000 at 12:55am and then only 100 at 1:00am on 03/13.  It may have climbed higher than that before it dipped, but by 1:00am it had dipped down to around 100 (coincidentally at 1am the CheckDB job had kicked off on a large database).

If you really need to know (in this example) exactly how high PLE gets before it dips, or exactly how low it dips, or at what specific time it valleys or dips, you need to actively watch or set up a collector with a more frequent collection.  You will find that in most cases this absolute value isn’t important – it is sufficient to know that a certain item peaks/valleys in a certain five minute interval, or that during a certain five minute interval (“The server was slow last night at 3am”) a value was in an acceptable/unacceptable range.

Andy also gives us a set of counters he uses by default and how to set up automated counter collection.  Left to the reader is integrating that into an administrator’s workflow.

Analysis Services Performance Monitoring

Bill Anton has a cheat sheet for SSAS performance monitoring extended events:

In this post we ran through the list of Extended Events that you’ll want to collect along with an explanation on the type of performance-related information you can derive from them. Depending on the type of instance you’re monitoring, we’re only really only talking about 5 (multidimensional) or 6 (tabular) events that need to be captured.

If you are in charge of administering Analysis Services cubes, Bill’s series is an excellent way of making sure you’re keeping up on good monitoring practices.

Azure SQL Database Security

James Serra has a number of links to Azure SQL Database security features:

Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases.  Azure SQL Database has many security features to make you sleep well at night:

Most of these are exactly the same as the on-premise product—at least the SQL Server 2016 version—but it goes to show that Azure SQL Database has grown up quite a bit.

Documenting Permissions

Chris Bell has a new script to audit SQL Server permissions:

I wanted the script to do a few things. Tell me who is in a AD Group that was granted rights. IT is one thing to see the group name, but that doesn’t really tell me who has access. I also wanted the output to be a little more user readable, so I formatted the output some. There are other things I did too, but you can fun reading through the code and comparing the 2 sources.

Knowing who’s allowed to do what is key to having a successful security posture.  This script won’t tell you object-level permissions, but at least gives you an idea about role and group membership.


February 2016
« Jan Mar »