We are a hardware and software provider to small government agencies, where many offices have just 1-3 computers, with a few others that are bigger having 5-12 workstations. For the small offices we set them up with a Win7 based peer to peer network, but for the bigger ones we give them a dedicated server, usually running SBS 2011.
We've encountered a problem where SQL backups are taking about 10x longer on machines running Windows Server OS as opposed to non-server OS. We run SQL 2012 Express to power our application. We brought in one of the problem machines for testing. With a Win7 Pro OS the backups happen very quickly, but on the exact same hardware running SBS 2011 and same database, a backup takes over 10 times as long.
What should we be looking for or what settings should we adjust to increase backup performance on a server OS?
Ok, we were able to find a way to improve backup performance. By specifying MAXTRANSFERSIZE = 1048576 we cut it in half. The strange part is, that number is supposed to be the default, as far as we can tell.
We were able to improve it again by another order of magnitude by specifying BLOCKSIZE = 65536