Sql-server – What are the best practices for backing up a secured SQL Server 2012

As with physical machines IO is king. There will be a noticeable IO hit from virtualisation (how much of a hit depends on the technology you choose), so make sure that you do nothing that can exacerbate this and harm IO performance further.

• Always used fixed size, not dynamic virtual disks, and ensure that the underlying drives/arrays are sufficient. Dynamic vdisks are less performant than fixed size ones.
• If the data in the VMs is not mission critical, turn on the VM solutions ability to cache writes (effectively lying to the guest OS about when things are physically written to disk) for extra performance. This is useful for a development and testing machine where the data is easily replaced, or read-only replicas local to the master that can be recreated if they experience any trouble, but it is recommended that such options stay off for production and backup systems as they increase the chance of data loss (or worse, corruption) if the machine powers off unexpectedly.
• Ensure that each VM has enough RAM dedicated to it such that the normal working set of each database fits into memory with a fair amount of room to spare to avoid unnecessary IO. Also, ensure that the hypervisor will not page out the VMs RAM at any time (for instance most VMWare products can do this, which allows you to run a larger set of VMs than you otherwise could on the same hardware, but the performance degradation can be massive - there is an option to tune or turn off this behaviour).
• If the host uses RAID5 arrays, consider moving to RAID10. This will reduce the space available but remove the write performance problems that can be apparent with RAID5 (or 6).
• For VMs that are likely to see a lot of IO activity, consider giving them their own drive or array, or only have them share their drive(s)/array(s) with data that sees low activity.

Of course if your entire DB (and everything else the VM is running) fits into the RAM allocated to the VM and it sees very little write activity, IO performance can be much less of an issue.

Edit: a few more points:

• If using virtual disks make sure that the best controller type is used for performance. Some virtualisation solutions offer several virtual controller types for better guest compatibility and you might find some less efficient than others due to protocol limitations in the design they are emulating and limitations in the guest OS's drivers.
• If your virtualisation solution offers specific drivers for the virtual drive controller, make sure your guest OS is using them - these can speed up bulk IO by reducing the number of memory-to-memory copies and host<->hypervisor<->guest context switches involved in some operations.
• The two points above apply if you are using network storage such as simple SMB shares or iSCSI too: make sure that you are using the most efficient virtual network controller on offer and that your guest OS is using the recommended driver for it, otherwise both latency and bandwidth may be more limited than needs be by the virtualisation layer.

We use them

• to separate permissions per client (eg we have desktop, WebGUI etc schemas)
• for logical grouping eg (Data and Staging schemas for our tables).

Useful and practical observations past the white paper mentioned by Marian:

• GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
• Nice groupings in SSMS Object Explorer
• OBJECT_SCHEMA_NAME
• You are forced to qualify object names (which is best practice)