Managing SQL Server Resources with Other Tools | Microsoft SQL Server 2000 High Availability

Other applications might have the ability to control the amount of memory, or, say, processor that your SQL Server instance can utilize. Is this recommended? In most cases, the answer is no. SQL Server does a good job of managing its own resources, and in some cases it has to. However, in Windows Server 2003, you can use a new feature called Windows System Resource Manager (WSRM). WSRM is supported in both stand-alone and clustered environments, although in a clustered environment, you will need to create or export the settings on one node and apply them to others. WSRM is a nonintrusive tool. You should use SQL Server s interfaces to manage processor affinity and memory, but using WSRM, you can allocate processor percentage for systems with multiple applications (such as a machine with SQL Server, Internet Information Services [IIS], and Microsoft Operations Manager [MOM]) or multiple SQL Server instances. You cannot do this amount of granular control through existing SQL Server- based tools. Do not use WSRM to do anything related to processor affinity or memory management for SQL Server.

Transferring Logins, Users, and Other Objects to the Standby

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.

Transferring Logins, Users, and Other Objects Between Instances

Although this topic was somewhat covered in Chapter 7, Log Shipping, 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 server- level 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.

Transferring Logins and Users

Moving logins from one instance to another is perhaps the easiest problem to tackle. One method, which is covered in the first task in section entitled Step 3: Post-Wizard Configuration Tasks in Chapter 7, is to use the DTS Transfer Logins task, with its corresponding task, to move the users by BCPing data out of syslogins.

If you do not move your logins, you will see an error similar to the following when applications or users try to access the database:

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

Orphaned Users

When you move over your logins, you might have what is known as an orphaned user. An orphaned user is one that exists in the database but whose system identifier (SID) does not match a login or exist as a login on the new server. To see if you have any orphaned users, you can execute the following Transact -SQL statement to give you a list of all orphaned users:

 exec sp_change_users_login 'Report'

Sp_helplogins might also assist you in your debugging process, as it will show you all information about a specific login. Another procedure that might help you is sp_validatelogins , which will tell you if you have Windows users or groups that no longer exist but are mapped to logins in SQL Server.

If you do have orphaned users, they should be able to access the new server, but they will not be able to access the database. This means that the SID is not matching, and you should see this error:

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

To map your logins, you can use a tool provided by Microsoft Support Services named MapSids, which can be found at http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/Mapsids.exe . This will create two stored procedures on your SQL Server. Follow the instructions found in Knowledge Base articles 240872, HOW TO: Resolve Permission Issues When You Move a Database Between Servers That Are Running SQL Server and 298897, SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When Database Is Moved on how to map your SIDs using these stored procedures.

Another method you can do once you have generated a report with sp_change_users_login is to use that stored procedure to try to fix your problems. Sp_change_users_login can take the following parameters:

@Action , which can have the values of Auto_Fix, Report, and Update_One. As of SQL Server Service Pack 3, Auto_Fix does require a password.
@UserNamePattern , which is the user in the database.
@LoginName , which is the SQL Server-level login.

For example, if you wanted to change the database login that the database user Fred is mapped to, you would execute the statement below. However, as of SP3, you cannot create logins with NULL passwords, so you will have to ensure that the login you are mapping to has a password.

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

Note

Keep in mind that transferring logins will not solve issues external to SQL Server. For example, if your standby is not in the same domain and you are using Windows Authentication, you need to ensure that the logins you are transferring will actually work on the standby. You might need to work with your network administrators to put things in place, such as two-way trusts between domains.

Transferring Objects

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, Replication.

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:

Select the Data Transformation Services folder in Enterprise Manager, right-click, and select the New Package option.
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.
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

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

Select the package and open it by double-clicking.
Under the Package menu, select Save As.
In the Save DTS Package dialog box shown in Figure 14-21, enter a name if one does not exist (that is, it was not previously saved), a password, and select where you want the DTS package to go. If you select Meta Data Services or SQL Server, the package will be saved to the msdb database of the SQL Server you designate in the Server drop-down list. You will then need to back up msdb. However, remember that you cannot restore another server s msdb database to another server unless you rename the server itself.

If you select Structured Storage File or Visual Basic File, you will be prompted to enter a file name with a .dts or .bas extension, respectively. If you do not put a path name, the package will be saved to your My Documents directory. For transporting packages between servers, saving the package as one of these two options is the preferred method.

Figure 14-21: Save DTS Package dialog box with Locations expanded.

Click OK.

Tip	Remember that DTS packages can be versioned and that after loading the packages onto another server, you might need to modify it, as some of the source or destination properties of some tasks will be assigned to the wrong server(s).