You can do several things to ensure that your physical SQL Server installations are secure.

If you do not change the passwords in the recommended way in Enterprise Manager, it might be possible to use the Services utility. The problem is that doing it there will not reset the correct registry keys or NTFS permissions in all cases. For a full list of what you would have to alter after using Services, see Knowledge Base article 283811, "HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000" (available at http://support.microsoft.com/).

As a database administrator (DBA), or as someone who will be performing database-related tasks, whether you like it or not, you are responsible for every application’s database in your environment. Although you might control the back end, developers control the front end. It is in your best interests to validate custom database-specific code and ensure that packaged applications will work with your security model. Here are some tips to consider:

You might find that your tasks (some or all) are so lightweight (good examples of this include DBCC CHECKDB with the PHYSICAL_ONLY option or DBCC INDEXDEFRAG) that you notice only a minor effect on performance during the scan.

On the other hand, if you drop and recreate an index, the build process will perform faster than a DBCC INDEXDEFRAG (especially if you have Microsoft SQL Server 2000 Enterprise Edition and you take advantage of the parallel index builds). But there is a cost: if it is a vital index, you will still see performance degradation until it is rebuilt, and if it is a clustered index, your data is completely unavailable while the index is rebuilt. In most cases, use the lightweight DBCC INDEXDEFRAG, which might take a little more time to complete because it runs without interfering as much with online processing.

Table 14-2 provides some tips about planning and running these medium impact commands. Run these as a SQL Profiler script on your test server and get a baseline of its performance. Then run each maintenance task and record the results. Compare them to the original and begin to put together a plan for maintenance and administration.

Another potential problem is any need to truncate and refresh a table in the production system. You can create two tables and alternate between them.

This is just one example, but you can introduce many design changes to make sure that services are not interrupted. The following is a brief history of how things have changed:

Some maintenance operations are more intrusive than others. By intrusive, it is meant that a table, or possibly the entire database, will be unavailable while the command is running. Notable ones include create index, rebuild index, full update statistics (auto update statistics does not cover all customer scenarios), and restore database. Customers must plan for these intrusions.

Table 14-3 provides some tips about planning and running these processes should you decide to run them; you should try less intrusive versions before running some of these commands.

Defragmenting indexes, whether small or large, might become a problem for you at some point. You do have a few options, but you need to weigh them carefully. All of them will have some impact on your system. The size of your database will definitely affect index defragmentation, since the larger the database is, the more complex the solution has to be.

Before you even attempt to defragment an index, you need to determine if you have fragmentation and if you need to actually do something about it. This is documented in the white paper.

If you need to defragment your indexes, one thing you could choose to do is to run a DBCC INDEXDEFRAG, but it might not be optimal for you. It can only defragment a single index. It can span files, but it only does one file at a time if you are using file sand filegroups, although you have to issue only one command. DBCC INDEXDEFRAG is an online operation, which means that you do not have to worry about users affecting the defragmentation process.

DBCC DBREINDEX can rebuild a single or even all indexes for one table. However, it is an offline operation—that is, the underlying tables will be unavailable for use while the DBREINDEX is happening, and you cannot generate any transaction log backups. Its advantage over an explicit DROP INDEX and CREATE INDEX is that you can rebuild indexes enforcing PRIMARY KEY or UNIQUE constraints without worrying about needing to recreate the constraints manually. Statistics are also rebuilt automatically and DBREINDEX will use multiple processors if they are available. You will also need to account for disk space during a DBREINDEX operation.

Logical fragmentation means that you have a fragmented SQL Server file internally where the data is stored but that the physical file on the file system is not fragmented. Your data can also be logically fragmented if you do many inserts and deletes. If your data is fragmented, you will need to find some way to compact your data so that it is contiguous and not fragmented. DBCC SHRINKDATABASE can shrink the database, but it is not coded to know about or reduce fragmentation; it only compacts things. DBCC SHRINKDATABASE will shrink a database even if it spans multiple files. Physical fragmentation can also occur if you are using your server for multiple applications and each one is using the same disk(s).

Physical fragmentation at the disk level can occur for SQL Server. With releases prior to SQL Server 7.0 that could not automatically grow, you physically had to create additional segments for your data or log. This could cause physical fragmentation over time. Now, with SQL Server 7.0 and SQL Server 2000, you can allow your databases to grow or shrink automatically, but you need to realize that these two can cause physical fragmentation at the disk level since you are expanding or shrinking your database files, or both.

This is directly related to placement of your data and log files. If you place all your files on the same spindle, your growth will occur on each database and expand (or contract, if shrinking) to the next available disk block(s). Over time, this can fragment your files. This is one good reason to place your disk files onto separate spindles. If you encounter severe physical fragmentation, you will have to consider defragmenting at the file system level. If you choose to do this, it is recommended that you make sure that no users are connected to SQL Server and possibly that SQL Server is shut down so you do not encounter any potential conflicts while the disk pages are moved around on the disk. It might be easier, however, to use a utility like sp_detach_db to copy and move the data and log files safely, reformat the disk so that it has a clean slate, and then copy the data back and reattach it.

How you approach physical fragmentation will vary depending on the size of your data, on whether other things will be affected, and on how fragmented things are. Make sure you back up your databases prior to initiating such an action.

In the end, you will have to test what works best in your environment to give you availability and performance, as well as the windows of opportunity you will need to perform the necessary maintenance.

Consider the following scenario: you have a database that log ships to another server every 15 minutes. Once a week, it is necessary to defragment the indexes on the database. Unfortunately, at the end of the process, you now have a 5 GB transaction log that takes too long to send and apply on your warm standby. To make matters worse, the database has 12 files. Also, you cannot control the fill factor directly with INDEXDEFRAG. If you consider DBCC DBREINDEX, you can control fill factor and it will span files, but it will cause a lot of blocking on the database at the time—and that only increases with size.

Ultimately, the right answer is the one that suits a company’s conditions. For the bullets listed previously, messing around with the fill factor of your indexes might be too granular, and, unless you really know what you are doing, you might cause more harm than good. Switching recovery models and moving data around and such is too much movement for some and might be risky for others. Disabling log shipping is a viable option, but you will still have the problem of a large transaction log after the defragmenting is complete. You are also exposed from an availability perspective, so you need to think about that. Alternating between DBCC DBREINDEX and DBCC INDEXDEFRAG is certainly viable, but unless you have a maintenance window to allow for DBREINDEX to happen, just doing DBCC INDEXDEFRAG might be your best option from an availability standpoint. At some point you might have to do a full DBREINDEX or DROP and CREATE INDEX, but you might be able to stave that off.

Although it is not common, you can still encounter database corruption with SQL Server 2000. Corruption usually happens due to some hardware error (good example: a bad or improper disk driver) that corrupts your SQL Server databases when data is written to them. A good indicator of corruption is 605 errors in your SQL Server error log. If you suspect disk corruption, scour Event Viewer as well as any hardware-specific monitoring tools to find out if you are seeing any errors outside of SQL Server. Whether you are seeing other messages or not, the best way to handle corruption is to call Microsoft PSS to have them help you assess your problems and walk you through the next steps. You do not want to accidentally cause additional problems by running DBCC commands that might not be necessary. At this point, you might be reliant on your backups and disaster recovery plans.

All versions of Windows up through Microsoft Windows 2000 and some versions of Microsoft Windows Server 2003 were primarily 32-bit operating systems until the release of the 64-bit versions of Windows Server 2003.

The amount of memory the operating system can address is directly tied to how many bits the operating system is. The 32-bit versions of Windows are able to address 2³² bytes of physical memory out of the box, or 4,294,967,296 bytes. To put it more plainly, Windows can address 4 GB of physical memory. The obvious implication is that the more address bits your operating system has, the more memory you can address. So a 64-bit operating system would be able to address, at most, 2⁶⁴ bits worth of physical memory (which is 18,446,744,073,709,551,616 bytes—an extremely large number). From this discussion, you would think that 32-bit versions of Windows are limited to supporting only 4 GB of physical memory, but that is not the case.

However, before you get excited and start writing or implementing applications that will require 4 GB of memory just to load, there are a few ground rules about how memory can be used. Windows utilizes something known as a memory split (see Figure 14-4). This means that by default, the kernel can utilize up to 2 GB of memory in its own separate memory space and each application can utilize up to 2 GB of memory in its own private address space.

Figure 14-4. An example of a memory split.

The memory manager works this way for two main reasons: self protection and application protection. The kernel mode gets 2 GB all to itself so that it will have enough room to do whatever it needs to do without running out of space. Plain and simple, applications will not run if the kernel space does not have enough memory. Along with this self-protection, user mode applications cannot address kernel mode space and accidentally corrupt kernel mode memory and cause an access violation (AV).

Notice that previously it was noted that each application can utilize up to 2 GB of memory. That might seem a little odd given that, natively, the operating system can recognize only 4 GB of total memory. For example, pretend that a server that has 4 GB of RAM (which we will call \myserver for the purpose of the example), and the kernel mode functions are using 1 GB of memory, leaving 3 GB for all other applications. Now, also imagine that an instance of SQL Server 2000 is active and using its full range of 2 GB of memory, leaving only 1 GB for all other applications. Now add Web services, native user mode applications, management and monitoring processes, and we are running at a full 3.9 GB of memory used. What happens when a new process is started that needs 200 MB of memory? This single request would put the processes over the actual amount of memory that the system has in total. So now, with each application getting its own 2 GB range, the memory picture begins to look a little more like Figure 14-5.

Figure 14-5. Memory split with a paging file in use.

To combat this hard limit of physical memory in the server, the memory manager uses disk space to substitute for physical memory. This disk space is called the paging file (pagefile.sys). Whenever there is not enough physical memory left to satisfy requests, memory that is currently not in use will be written to the paging file on disk, freeing physical memory to satisfy requests (actually, the process is somewhat more complicated than described here, but for this discussion, it is sufficient).

So far, there can be 4 GB of memory in the operating system with 2 GB available to kernel mode functions and 2 GB available to each user mode application. If physical memory becomes constrained, items in memory that have not been recently used will be written out to a pagefile to free physical memory for more pressing needs, and the memory picture looks more like Figure 14-6. So what happens when an application needs some of its memory that has been written out to the paging file?

Figure 14-6. Freeing physical memory.

To enable applications to not have to keep track of memory in case it gets moved or worry about overwriting the memory of other applications and processes, the memory manager virtualized all the memory addresses used by user mode applications and processes. For example, imagine that we have two processes, process A and process B, and both ask for 1 MB of memory. Both processes get an answer from the memory manager that they have been allocated 1 MB of memory starting at address 0x28394532. What happens when both applications write to that address? Will one of the processes erroneously overwrite the other’s data? Will one of the processes receive an error? Will one or both of the applications crash?

The answer is none of the above. Both processes will be able to write to the same address because of the virtualization that goes on behind the scenes. User mode processes are never able to directly write to physical memory and never actually know where their data resides. A user mode processes and requests a block of memory and writes to it. In the meantime, the memory can be written out to the pagefile, and when the application does something with the memory, the memory manager will go out to the pagefile and retrieve the data from the pagefile for the application. The key thing here is that the application never knew anything about the virtualization process; it simply thinks it is writing to a memory location.

How does an application get its memory back once it is in the paging file? It does not get it back. Whenever an application references memory in the paging file, the memory manager will retrieve the data out of the paging file for the application because, remember, the application is not responsible for keeping track of where the data really is.

Because of this virtualization, each application can write to the virtual locations of 0x00000000 – 0x7ffffffff (or 2 GB) without affecting any other process. Each application thinks that its memory location 0x38344134 is the only one on the server, when, in fact, many applications are simultaneously using that virtual location. All the while, in the background, the memory manager is writing memory for those applications and keeping track of where they are in physical memory and in the pagefile.

Keeping this in mind, the previous statement that the kernel space and each application could utilize up to 2 GB of memory was somewhat incorrect. Now that we have introduced the concept of virtual memory, it is far more accurate to state that the kernel mode space, as well as each application, can utilize up to 2 GB of virtual memory. Certain portions of the memory manager, however, cannot be paged to disk.

The kernel address space can also use 2 GB of address virtual memory, but the key difference is that all processes in the kernel space share the same 2 GB. Each process does not get its own unique memory space use without affecting other kernel mode processes. Any user mode thread that enters and does its work in this space and then returns will lose access to the memory it just used. Because of this, it is very important for drivers and kernel processes to be very good at handling memory. When rogue processes corrupt or overwrite kernel address space memory, the results are disastrous indeed. Errors in the kernel mode space, where most hardware driver code exists, often lead to a dreaded system crash if the right set of conditions occurs. In Windows 2000 and later, this is less likely to happen due to protection mechanisms built into the operating system.

Most memory in the kernel mode space is virtualized. Some memory, depending on the function, can be paged out to disk as well. There is a special type of memory that cannot be paged out to disk. One of the key items that resides in that space is the portion of the memory manager that handles virtual address translation and its related items and drivers for disk access. Those must remain resident in physical memory at all times (this is addressed later).

Memory access is always slow compared to memory access from physical memory. For example, imagine that an application named memoryhog.exe references three pieces of data it has stored in memory: Mem1, Mem2, and Mem3. Mem1 is right where memoryhog.exe left it. The memory manager has not shuffled it around at all. The memory manger has not moved Mem2 either, but the virtual memory address no longer points to the data that is still in physical memory. The data is not out on the paging file yet. The memory manager, however, has moved Mem3 into the paging file. memoryhog.exe does not know this, of course—it simply references the memory virtual addresses it wrote to in the first place. Which reference will occur faster and why? The answer is Mem1, and here is why.

When memoryhog.exe references Mem1, nothing "extra" happens. It is right where memoryhog.exe left it, and no extra looking and retrieving goes on. When Mem2 is referenced, it is a little slower because the memory manager has to repoint the virtual address to the physical memory, but the performance hit is negligible because the item still remained in physical memory. Mem3, however, must be retrieved from disk, and it is the slowest of all. Disk access times are measured in milliseconds and memory access time is measured in nanoseconds, so every time memory has to be retrieved from disk, it takes approximately 1 million times longer to retrieve the data than if it had still been resident in physical memory.

Whenever data is not where an application originally left it and the memory manager has to retrieve the data, a page fault—which means that you are now accessing a physical disk—is incurred. You must be aware of two types of page faults: soft page faults, as occurred with Mem2, and hard page faults, as occurred with Mem3.

Because hard page faults are devastating to system performance, operating system–based solutions were developed to help applications use and retain more data in physical memory.

Because databases are growing so large and usage is heavy, the default 2 GB that a single user mode process can utilize is often insufficient for real-world needs. In order to combat this and increase performance (or to prevent performance degradation due to increased load), you will need to tune the amount of memory you are using for a given SQL Server 2000 instance.

The following memory tuning options are discussed in this section:

Each option does not function the same but has the same goal: to increase performance of an application by modifying the way the memory manager works to reduce disk paging.

multi(0)disk(0)rdisk(0)partition(1)WINDOWS='Windows Server 2003, Enterprise'
/fastdetect /3GB

multi(0)disk(0)rdisk(0)partition(1)WINDOWS='Windows Server 2003, Enterprise'
/fastdetect /3GB /userva=2560

multi(0)disk(0)rdisk(0)partition(2)WINNT='Windows 2000 Advanced Server' /PAE
/basevideo /sos

EXEC sp_configure 'awe enabled', 1
RECONFIGURE

As noted above, the operating system uses the paging file, which is a physical file located on your disk subsystem, for some memory operations. The general rule of thumb is to size it at 1.5 times the amount of physical memory that you have. However, in Windows 2000, there is a maximum of 4 GB for the page file size. A paging file size of 2050 MB (just over 2 GB) is the minimum file size necessary for computers with 4 GB or more of physical memory. In Windows 2000 and Windows Server 2003 32-bit, there is a maximum file size of 4 GB, but there is no limit in 64-bit versions of the operating system. In Windows Server 2003 64-bit, you can have a paging file size much larger than the 2050 MB, and it is typically three times the physical RAM. To do this, you have two options:

Option 2: Through the Registry

1. In the location where you will create the extra paging files, create folders for each additional paging file.

2. Start the Registry Editor with Regedt32.exe, and find the following registry key:

   HKEY_LOCAL_MACHINESystemCurrentControlSetControl
   SessionManagerMemoryManagement

3. Under the key, find the Pagingfiles value, and double-click it. Delete any entries that exist and add entries for each of the files you will be using in the folders you created. For example:

   c:pagefile1pagefile.sys 3000 4000
   c:pagefile2pagefile.sys 3000 4000

4. When you are finished, click OK and exit the Registry Editor. You must restart the operating system for the new changes to take effect; this is not a dynamic change.

5. After the system is rebooted, verify the new settings using the instructions for the GUI to see the changes in the Virtual Memory dialog box.

In terms of placing the pagefile.sys, it is recommended that you do not place it on any disk that will have SQL Server databases, because if you run out of memory and start paging, you could have serious I/O contention with your SQL Server databases and have a double performance problem (memory and disk). You might choose to use a form of redundant array of independent disks (RAID) to protect your paging files, and that is absolutely required if you split your paging files among multiple disks. RAID 1 would work well.

Also, how you configure your paging file is directly related to how you set the Startup and Recovery settings in Computer Management, which you can find in the Advanced tab when you right-click Computer Management (Local) and select the Properties. If you choose either the Kernel Memory Dump or Complete Memory Dump option and select the option Overwrite Any Existing File, Windows will write over any existing dump file and use the same name. If you do not select Overwrite Any Existing File, you will have the ability to save all your memory dumps, but you will have to account for the space. See Figure 14-9 for the Startup and Recovery settings in Computer Management.

Figure 14-9. Startup And Recovery dialog box from Computer Management.

If you want to get full memory dumps after a crash with 32-bit systems larger than 4 GB, you must add the /MAXMEM switch in boot.ini like this:

multi(0)disk(0)rdisk(0)partition(1)WINDOWS='Windows 2000 Advanced Server'
/fastdetect /MAXMEM= n

where n is the amount of memory you are specifying as the maximum amount of memory that the operating system can use.

Caution

/MAXMEM was originally designed for stress testing and debugging, so use this with extreme caution.

For example, if you put /MAXMEM=128, you would be restricting the operating system to only 128 MB of memory, and no matter how much more memory you have in your server, that is all it would see. Do not set this lower than 128 MB for Windows 2000 versions of the server or for Windows Server 2003, Enterprise Edition. Windows Server 2003, Datacenter Edition, has a minimum requirement of 512 MB of memory. Realistically, if you use /MAXMEM, you should set it to the minimum recommended RAM, not the bare minimum needed.

Important

Using /MAXMEM requires that one of the paging files on the system must be at least 1 MB larger than your physical memory; otherwise you will not get a full memory dump.

Warning

/MAXMEM is an undocumented boot.ini switch that you can find in Volume 3 of the Windows NT Resource Kit. It is meant only for specific configurations, and the memory you specify must be contiguous. This option is valid through Windows 2000 and is not guaranteed to be in future operating systems. Do not use the /MAXMEM option without careful consideration and thorough testing.

The toughest part about understanding the memory models is putting it into practice. What do you do for your SQL Server instances? This becomes more glaring on a server cluster where you are trying to implement more than one instance or where you are trying to consolidate multiple SQL Servers onto one or more instances, possibly on a single server or a cluster. Use Table 14-4 as well as Table 14-5 for 64-bit versions to see how much memory you can use under your operating system of choice.

64-bit SQL Server 2000 changes the rules a bit. All memory under 64-bit is dynamic, and you have much more available to you. In this case, whether you are on a cluster or using a stand-alone server, you can allow SQL Server to manage its own memory, and you can set the MIN SERVER MEMORY option of SQL Server to set the minimum amount of memory for each instance. To set this through Transact-SQL, here is the syntax:

EXEC sp_configure 'min server memory', 8000
GO

Conversely, you can also set your memory settings in Enterprise Manager in the Memory tab of the instance’s properties, as shown in Figure 14-10.

Figure 14-10. Memory tab of an instance’s properties.

Using the Memory tab, you can set the following: if you select Dynamically Configure SQL Server Memory, you can set a maximum or minimum amount of memory, or both, that the SQL Server instance can use. By default, the maximum is set to the total amount of memory SQL Server can use and the minimum is set to 0, meaning SQL Server can use up to every amount of available memory. If you are using this option, when you set Minimum (MB) on the screen, it corresponds to executing sp_configure with MIN SERVER MEMORY, and Maximum (MB) is the same as issuing sp_configure with MAX SERVER MEMORY.

If you select the option Reserve Physical Memory For SQL Server, this will physically reserve that amount of memory for SQL Server and not let it go until you reconfigure (and subsequently restart) SQL Server. You should be setting this option only when you also select the option Use A Fixed Memory Size (MB) with an appropriate number. Behind the scenes, when this combination is set, SQL Server is issuing sp_configure twice, once with MAX SERVER MEMORY, and once with SET WORKING SET SIZE (setting that to 1). Setting this option also requires a stop and a restart of SQL Server, as shown in the prompt in Figure 14-11.

Figure 14-11. Prompt after selecting Reserve Physical Memory For SQL Server.

If you are using /3GB only, you can allow SQL Server to manage memory dynamically up to 3 GB, or you can set a static amount. Nothing special is required. However, if you are using AWE memory, as mentioned earlier, you must set the option awe enabled to 1, as well as set working set size to 1 to allow SQL Server to physically reserve the amount of memory. Here is what the syntax looks like:

EXEC sp_configure 'awe enabled', 1
RECONFIGURE
EXEC sp_configure 'set working set size', 1
RECONFIGURE

Then you will need to set the options Max Server Memory and Min Server Memory to the same number.

EXEC sp_configure 'min server memory', 8000
RECONFIGURE
EXEC sp_configure 'max server memory', 8000
RECONFIGURE

Once all this is done and you restart your SQL Server instance, you will be able to take advantage of your memory above 4 GB with AWE.

When you are looking to tune your memory used for SQL Server, when can you combine /3GB with AWE under 32-bit? Follow Table 14-6.

Memory and Multiple Instances on the Same Server or Server Cluster

One thing many people struggle with is how to set memory for a clustered instance or multiple clustered instances running as part of a server cluster or if you have multiple instances on one Windows installation. In addition to the considerations listed in the previous section, for a cluster you now need to take failover into account. If you have a single instance cluster, assuming all nodes are equal in capacity and are configured the same, you will not have to worry about anything. During a failover, the SQL Server should start up and behave exactly the same way.

Multiple instances are different. Why? In a failover, your goal should be to ensure that during the failover, when the instance is restarted on another node, it will have the correct capacity from a processor and memory standpoint (this section will deal only with memory; see Chapter 6, for details on processors). Here is where the version of your operating system greatly plays into how you ultimately configure your memory for each SQL Server virtual server. For example, if you are using Windows 2000 Advanced Server, you are limited to 8 GB of memory, eight processors, and two nodes. If you need two, three, four, or even more instances, this could be challenging to say the least, especially if they all need more than 4 GB of memory each! Switching to Windows Server 2003 Enterprise Edition, you can now have up to 32 GB of memory (not to mention two more nodes that would be available to you).

With multiple instances on a stand-alone server, all instances need to play well together in the proverbial sandbox. Although you do not need to worry about failover, you do need to worry about performance and about ensuring that you are not starving one instance to feed another. Your operating choice and the amount of memory it can handle will influence the design of your instances, and, specifically, the amount of memory you can allocate.

Think of your instances as glasses of liquid. If you have two glasses that are half-full, you can pour the contents of one into the other. If you have two full glasses, you cannot combine them. This is how your SQL Servers will behave in a failover or when you combine instances on one server. To prevent this, no matter what you employ (PAE/AWE or /3GB), you should set the Max Server Memory option on each instance to physically cap the amount of memory each instance will get.

Consider this example: you have a two-node cluster with three SQL Server instances. Instance 1, which currently resides on Node 1, has 7 GB of memory configured using AWE. Node 2 houses Instance 2, which has 5 GB of memory allocated with AWE, and one instance that is not using any advanced options and is currently using 1.5 GB of memory. Each node individually has a total of 8 GB of physical memory. This is shown in Figure 14-12.

Figure 14-12. Nodes prior to failover.

A problem occurs on Node 1, causing a failover. The instance tries to restart on Node 2, but it cannot. You now have a failed set of SQL Server resources that cannot come online, causing an availability problem. Why? Well, to use AWE, you need to guarantee that the memory you told SQL Server to use will be there. A failover is basically a stop and start on another server. In this case, you were already using 6.5 of the available 8 GB of memory. 6.5 + 7 does not equal 8, and it does not matter how big your page file is. Your instance will not grab the memory for AWE. It might, however, start up, but it will only grab the amount of memory that it can up to 2 GB (see earlier for how AWE behaves in a failover). Now you are risking memory starvation of the operating system, and this can affect all instances. This is shown in Figure 14-13.

Figure 14-13. Nodes after failover.

So how do you prevent this? By planning, these are the types of scenarios you need to play out in your head when proposing the number of instances on a cluster. Your management might see a two-node cluster and say, "Hey, why is this second server doing nothing? Get something on there!", leaving you in the state of the previous example.

The way you can balance memory in this case would be to give two instances that need a fixed amount of memory 3 GB each and let the other one be dynamic (or set it to 1 GB or 1.5 GB to allow room for the operating system). If you cannot live with this, you will need another cluster or set of servers to handle this workload. The recommendation was basically to halve the memory, which might lead some to believe that you are wasting resources, but again, is performance or availability your goal? By definition, if you are reading this book and implementing things like a cluster, you are probably saying that availability is your highest priority. If you never have to failover and you left the instances at 7 GB, 5 GB, and dynamic, things would work great. But once a failover happened, all bets would be off.

64-bit does change the rules, as stated above. Because all memory is dynamic and you have much more available to you, you should set a fixed minimum amount of memory that SQL Server will need to use and let SQL Server manage anything else it needs. This way, you will always guarantee that your instance will have enough memory to do what it needs to do, but you are giving it the option of grabbing more memory if necessary.

Important

When employing AWE, using 64-bit, or setting a fixed amount of memory, the SQL Server instance upon startup will need to physically reserve the configured amount of physical memory. This process is not instantaneous. The more memory you allocate to SQL Server, the more time it will take to start. In a failover scenario, it will definitely increase your failover time, so take that into account. It is usually measured in minutes, rather than seconds.

When you are looking to have a server function as a standby, it needs to be functionally in sync with your production database. This means that logins and any other objects that exist on your primary database have to be on your standby. With failover clustering, that is not a worry, as the entire instance moves to another node, so you get not only the same database, but also essentially the same server. With log shipping, replication, and possibly backup and restore, you are not guaranteed to get all objects on your standby, and you will have to institute processes to ensure that in the event of a problem, you can flip the switch and users will not notice any difference in your standby.

Although this topic was somewhat covered in Chapter 7, with a more focused approach on log shipping, this is a generic problem that you will have with warm standbys. You have two levels to worry about: the serverlevel login, as well as the user that is in the database. If you transfer just the database login, your users might not be able to connect because the login it is associated with does not exist in SQL Server.

Msg 18456, Level 16, State 1
Login failed for user '%ls'.

exec sp_change_users_login 'Report'

Server: Msg 916, Level 14, State 1, Line1
Server user '%.*ls' is not a valid user in database '%.*ls'.

Exec sp_change_users_login 'Update_One', 'Fred', 'FredNewLogin'

There are two ways to move objects between instances: SQL scripts and Data Transformation Services (DTS) packages.

Generating SQL Scripts

Database objects come in a variety of categories—stored procedures, table definitions, permissions, jobs, triggers, and so on. One of the easiest and most recommended ways of moving objects is to generate a SQL script. The script would be one that you coded to initially get the object into SQL Server, or, if you used the GUI of Enterprise Manager, using Enterprise Manager to generate the script. You can script most objects created within Enterprise Manager directly by selecting it, right-clicking, selecting All Tasks, and then selecting Generate SQL Script. Three tabs are in the Generate SQL Scripts dialog box.

Tip

SQL Server Agent jobs, Alerts, and Operators can also be scripted this way. Scripting replication was covered in Chapter 8.

The first tab of the Generate SQL Scripts dialog box, General, is where you select the objects that will be scripted. An example is shown in Figure 14-14.

Figure 14-14. General tab of Generate SQL Scripts.

The Formatting tab, shown in Figure 14-15, allows you to control how your files look after they are scripted.

Figure 14-15. Formatting tab of Generate SQL Scripts.

The Options tab shown in Figure 14-16 is an important tab. It is where you select primary/foreign keys, permissions, logins, and such to be included in the .sql file. If you do not select options such as Script Object-Level Permissions, you might not be able to correctly use the script if you ever need to use it as, say, part of a disaster recovery plan.

Figure 14-16. Options tab of Generate SQL Scripts.

When you are done, click OK, and you will be prompted for a location in which to save your .sql file.

If you are scripting SQL Server Agent Jobs, Alerts, or Operators, the dialog box is a bit different. The three are nearly identical, and the Jobs dialog box is shown in Figure 14-17. However, unlike generating scripts for all jobs at once, you do it for each job. You can then combine the output scripts into one master script, but there is no way through the GUI to select more than one job at once.

Figure 14-17. Generate SQL Scripts windows for SQL Server Agent Jobs.

Note

If you are employing log shipping, all objects captured in the transaction log will be automatically transferred to the warm standby. See Chapter 7 for more details.

Using a DTS Package to Transfer Objects

You can use a Data Transformation Services (DTS) package to send certain objects to another SQL Server. In SQL Server 2000, you can transfer logins (as covered in Chapter 7; this is represented by a small gray server icon with a person’s head on it), jobs (represented by a miniature dialog box with a small circle, or clock, in it), error messages (represented by a person’s head and a small thing that looks like a piece of paper with an x in it), and objects (represented by two gray servers with a red arrow pointing from one to the other). You can see these in Figure 14-18.

Figure 14-18. DTS package with transfer tasks.

Important

DTS packages do not run, and Dtsrun.exe was not ported to SQL Server 2000 64-bit. Under 64-bit, you have the option to create and run DTS packages on 32-bit that can take data from and send data to 64-bit. You can save a DTS package to a 64-bit SQL Server 2000 instance, but you cannot run it on 64-bit. If you have SQL Server 2000 Service Pack 3 or later installed, the option to schedule the package is disabled since you cannot run the package under 64-bit.

The Transfer Logins Task, Transfer Jobs Task, and the Transfer Error Messages Task all have similar dialog boxes: they have three tabs—Source, Destination, and a tab to select the login(s)/job(s)/or message(s) you want to send. An example is shown in Figure 14-19.

Figure 14-19. Transfer Jobs Task dialog box—Jobs Tab.

The Copy SQL Server Objects Task dialog box is different. It also has three tabs, but the third tab, Copy, is more like the Generate SQL Scripts dialog box. An example is shown in Figure 14-20. By default, this task will also copy your data. You will want to deselect the Copy Data option. By default, it will also copy all objects and use default options. If you want to send only selected objects, clear Copy All Objects and click Select Objects. You will then be presented with the Select Objects dialog box to choose what you want. Similarly, if you want to control the default options that will govern the task, clear Use Default Options and click Options. You will see the Advanced Copy Options dialog box.

Figure 14-20. Copy SQL Server Objects Task—Copy tab.

To create a DTS package, do the following:

1. Select the Data Transformation Services folder in Enterprise Manager, right-click, and select the New Package option.

2. Select the tasks you want to add to your DTS package from the icons at the left side of the DTS Package dialog box, or select them from the Task menu.

3. Once you are finished adding and configuring your tasks, save the package by selecting the Save or Save As options from the Package menu. You can now schedule this package to run on a regular basis.

DTS packages are a bit different. They are not transferred through the transaction log. To move a DTS package, follow these steps: