Q In addition to hyperthreading and dual-core technology, chip vendors are beginning to release processors with additional cores (four, eight, and more). I am considering purchasing a new server with multi-core processors to support a SQL Server 2005 Standard Edition deployment and am curious if, when using a four-core processor, I will only be able to make use of a single physical CPU (since Standard Edition is limited to four CPUs)?
A For the purposes of both licensing and CPU edition support, SQL Server considers only the number of physical sockets/CPUs, regardless of the number of cores on the processor. So, for example, the fact that SQL Server 2005 Standard Edition supports up to 4 CPUs means it will support 4 physical CPU sockets, regardless of the number of cores in each one (if you have 4 physical CPUs with 4 cores each, your Standard Edition deployment would have 16 logical CPUs to make use of). Moreover, even though you have 16 cores/logical CPUs, the licensing requires only that you pay for the 4 physical CPUs, not all 16 cores. For more information on SQL Server and multi-core, see
Q I’m about to purchase some new database servers. Should my organization go with 64-bit or stick to the tried and tested 32-bit versions?
A It depends. This is a very common question now as vendors transform their hardware from x86 to x64. The first question you should ask is what the memory pressures on your database server will be. If your SQL Server instance will consume less than 3GB of RAM over the life of the hardware (typically three years), then 32-bit x86 is reasonable provided that will be the direction for other servers (domain controllers, DNS servers, application servers, Web servers, mail servers). If the SQL Server instance will consume more than 16GB or there will be multiple instances per server (or cluster), you should seriously consider a move to 64-bit technologies. If there will be more than eight processors, IA64 is the standard recommendation. However, as x64 four- and eight-core CPUs start hitting the streets in quantity, there may be a significant cost argument for going with x64 rather than IA64.
When moving to x64 (or IA64), deploying the same architecture in development, test, and performance environments may not have been included in initial cost estimates. But you should also consider whether you want to get stuck with old technology halfway through your hardware lifecycle. In 18 months it will be almost impossible to source purely x86 hardware. If you go with that kind of solution now, upgrading over the next year or two will be expensive as it will be a rip-and-replace scenario. Going for 64-bit hardware now will give you more options over the next few years.
So a good time to make the jump to 64-bit is when you’re purchasing new hardware, at the commencement of a core application release cycle, or when you’re upgrading from SQL Server 2000 to SQL Server 2005.
Q I have log shipping enabled between two servers. Over the weekend, a hardware problem on the primary server caused log shipping to stop. When I returned to monitor log shipping, I could see that the LS_backup_dbname was running fine despite several hours of downtime.
On the secondary server, however, the copy job appears to be working fine but the restore job seems to be skipping files and eventually failing over and over again. It runs, but skips every file and then fails. So my time since last restore is now 849 minutes. What I’ve done in the past in similar situations is simply re-initiate the shipping configuration—that is, I turn it off and turn it on again so I wind up taking a fresh backup and restoring it to the secondary server, and log shipping begins again. Is there a better way to manage this scenario?
A What you are probably seeing is the effect of having missing backup files. As you know, the log shipping backup, copy, and restore jobs run independently. Old backup files in the copy location are also removed based on the schedule you specify. If you had a period of time where the restore job was not running, it might have exceeded the interval, which would result in missing backup files. The log shipping restore job has logic that tries to recover from different types of failures caused by different operational conditions. Basically, it scans through the files when errors are encountered and tries to find the correct backup files. If a correct backup file is not found, I suspect something has removed the needed files or possibly you reconfigured the backup part of log shipping?
One solution would be to manually restore up through the transaction log backup files that were inadvertently created by the other backup job. The problem with just copying them to the log shipping copy location is the name conventions recognized by log shipping. But once you have manually restored through that point, the normal restore should pick up and continue.
Q I need to know how to use T-SQL to find the size of total and free physical memory. Is there an easy way to get this information?
A Just run the query in Figure 2 and you’ll get the data you’re looking for.