SQL Q&ADaylight Saving T ime, Server Memory, and More

Edited by Nancy Michell

Daylight Saving Time

Q Given the upcoming changes to Daylight Saving Time (DST) in the U.S. to comply with the Energy Policy Act of 2005, should I update SQL Server™?

A No. At this time there are no SQL Server-specific updates required to support the changes to DST. SQL Server relies on the underlying operating system for time-related data, which means if the OS is reporting the correct dates and times, SQL Server will report and use the same values. To comply with the upcoming changes to DST, you will be required to update your copies of Windows® as outlined at support.microsoft.com/kb/928388. This will be required on all Windows operating systems prior to Windows Vista™ (which already includes the changes) to comply with the DST changes, including those running SQL Server. (Australia is also experiencing some changes. See support.microsoft.com/kb/912475.)

Connecting with Windows Vista

Q I've installed Windows Vista and now I can't connect to SQL Server 2005 on my system. I'm a local administrator, and I used to be able to make this connection without any problem. What has happened?

A This is expected behavior in Windows Vista and SQL Server 2005 without Service Pack 2 (SP2). Windows Vista has a new security model (User Account Control) that in effect traps your membership in the local administrator's group and asks you to verify operations requiring administrative privileges. If you click the Allow button, your credentials, which include your administrator token, are sent along to the application. In the case of the SQL Server Management Studio (SSMS), you're not shown a dialog because just running the tool doesn't require administrative permissions. The problem is that by default the SQL Server 2005 system administrator role includes the local administrators group from the operating system, and that is what your account used in the past to access SQL Server. Since Windows Vista isn't sending that permission along, you don't gain access.

It should be noted that running SQL Server 2005 without SP2 isn't supported on Windows Vista, and that SP2 has a tool that will add your account for you. If you're still waiting for SP2, the fix is pretty simple. Your individual Windows user account must be added to SQL Server, in your case to the system administrator's role. To do this, just right-click the SQL Server Management Studio and select Run as Administrator. Connect to your SQL Server and add your Windows account to the system administrator's role. See SQL Server Books Online for more information on how that works.

Transactional Replication with Views

Q If I have a published view and I update it, I know that transaction will be replicated. If I update the base table of this view, however, will that transaction be replicated? Also, if I have a published table and create a view of it, and I update the view instead of the base table, will that transaction replicate?

A Assuming the base table is configured as an article in a publication (that is, you configured the base table for replication as well), then any update to the base table would be replicated.

When you replicate a view, by default all that is replicated as part of a view is the schema portion of the view, or the code behind the view, not the data that underlies it (unless you have an indexed view). Then, even without replication, any time you update a view-in this case that means running a Data Manipulation Language (DML) statement with the view as the target-you are really just updating data in the underlying table, not the view. A view is just a logical storage of a query statement with no physical storage attached to it (again, unless you are using indexed views).

Max Server Memory

Q I have a box running Windows Server® 2003 and SQL Server 2000, with 5GB of RAM. Let's say I use the /3GB switch to increase my user mode virtual address space, the /PAE switch to load the Physical Address Extension (PAE) version of the Windows kernel, and set Address Windowing Extensions (AWE) Enabled to 1 (and enable lock pages in memory). With AWE enabled, will the max server memory option configure just the size of the data cache or the size of all buffer caches (data, processes, sessions, and so on)? Since only the data cache can make use of AWE-mapped memory, if I configure the max server memory to 4GB, will the data cache use only 1GB (the portion mapped by AWE), or can it use this extra 1GB and continue to use or compete with all other memory consumers in the standard address space?

A Max server memory will always limit the entire buffer pool size; however, the only consumer that can make use of AWE-mapped memory is the data cache, nothing else.

So for your first question, even with AWE enabled, max server memory still limits the entire buffer pool, but non-data-cache consumers won't ever use any of the AWE-mapped memory.

For the second question, the data cache will use AWE-mapped memory plus any other memory in the buffer pool that SQL Server determines appropriate; it won't be limited to only AWE memory, it just happens to be the only consumer that can make use of AWE memory. If you're not sure what the /3GB switch does, see Raymond Chen's blog.

Profiling and Performance

Q I have SQL Server 2005 mirrored in production. When I start SQL Server Profiler on the database machine and write the trace data to a file, I see a drastic drop in performance. Why?

A The reason for the performance drop depends on where you're running Profiler. If you're running it interactively on the server machine, then the Profiler UI is consuming both memory and CPU on the server, which will impact performance.

If you're running Profiler interactively on a workstation, then you're moving the event information across the network. This impacts throughput. If this is the same network you're using for mirroring, you'll see an impact there as well. Also, if you're storing the profiler output on a network share, you're moving data across the network and adversely affecting performance.

Probably the best way to mitigate all of this is to run Profiler non-interactively on the server that is running the instance to be profiled and then pipe the output to a local file. You'll still be consuming server resources but this approach generally has the lowest impact. This works much better than the (in-memory) Profiler trace. File trace uses the system memory more efficiently; it has larger buffers and flushes them to disk more effectively. It also doesn't depend on external processes (like SQL Server Profiler).

Finally, trace data is written to a disk file while Profiler is still profiling. The trace file is shared so that other people can see real-time profile data remotely. If you're calling the trace file interactively, that means you've manually invoked Profiler and are viewing output on the screen. Traces can be created programmatically with no visual output, which is why you should run things non-interactively.

You can create a share on top of a local directory and other people can access the files found there-that's typically fine. As mentioned before, you don't want to send trace output to a file on a remote share, especially one that is accessed through the same network pipe used for mirroring.

You should also select just the minimum set of events needed for your investigation. For the trace file location, you should choose the fastest drive on your system (preferably different from the SQL Server database and transaction log drives). If you still experience significant performance degradation, split the events into two or more traces each targeting a different hard drive. Even if the traces target the same hard drive, you'll still benefit from a split due to the fact that each trace has its own set of buffers. See sp_trace_create and all of its relatives in SQL Server Books Online for more.

Clustering Issues

Q I am trying to install SQL Server on a cluster running Windows Server 2003. Every time I try this, I get an error stating "Setup failed to perform required operations on the cluster nodes." The sqlstp.log simply states:

Script file copied to '\\SERVER01\ADMIN$\SERVER01_MSSQLSERVER.iss' successfully. Installing remote service (SERVER01)... An error occured in the service create (SERVER01): 1069...

What could be happening?

A There are several possible reasons for this failure. Setup installs Windows NT® Service on all selected nodes to remotely manage the setup process on individual machines. Therefore, you have to be aware of a number of snags you may encounter.

First, your domain account users may have a group policy that denies "log on as service" permission. (Remember domain policy overrides your local machine policy.) Make sure you're using an account without these restrictions.

Second, the logged-on account of the machine you're executing setup from should have admin access on all nodes because of the following: the Master setup process requires remote registry access to all machines; setup copies cnvsvc.exe to the remote machine's Windows directory; or setup uses standard Windows APIs that use only the logged on account's permission to access remote machines. For these reasons you should be logged on as an admin on all modes by default.

Disaster Recovery Plan

Q I am considering whether to use database mirroring (async mode) or log shipping to implement my disaster recovery (DR) strategy for my SAP databases. My production and DR sites will have a 100Mb broadband connection that is not dedicated to the mirroring session. The connection will be shared with different mirroring sessions or even with other DR servers.

If there is a network issue preventing the log record from being sent to the mirror database, will there be a retry?

When the mirroring session is suspended, is there any retention period? And, besides the system views, is there any logging information I can use to monitor the mirroring traffic and the log records transmission?

A Let's start by answering the question: what is the retry logic of database mirroring? You can look at this in two ways: First, if there is a transient network issue, the mirroring session state is disconnected. There is a default network timeout value of 10 seconds, which means a log record can't be sent from the principal database to the mirror database. In such cases, the principal will continue running "exposed" and the transaction will commit on the client side. Once the network issue is resolved, the mirroring session will retry automatically without any user intervention. It will attempt to catch up using the log records; the partners will synchronize first and once they catch up, the state will be synchronized.

Second, if there is a redo issue, the mirroring session state is suspended. A redo issue means that the mirror database cannot commit the log records on its database. Redo issues are mainly caused by a physical file not being found or by insufficient disk space. In such cases, the principal will continue running exposed and so the transaction will commit on the client side. After you fix the redo issue manually on the mirror server, the mirror session requires intervention:

ALTER DATABASE <db> SET PARTNER RESUME;

Concerning retention periods, the answer is that regardless of whether the mirror session is disconnected or suspended, the log records will be kept until the session is restored and all records from the time the session was suspended until the time it was resumed are hardened on the mirror. Basically, while the mirroring session is disconnected or suspended, the log on the principal cannot be truncated, because that would break the log redo chain. This means that the principal's log will grow unbounded until the session is restored. Therefore, there is in fact no retention limit on the mirroring session; the only real constraint is the disk space the principal server has for storing the log since the log cannot be truncated.

Finally, there is no specific log file you can use to monitor mirroring. SQL Server 2005 provides a GUI tool called the Database Mirroring Monitor for this purpose, which lets system administrators view and update status and configure warning thresholds on several key performance metrics. From this, you can get an alert when mirroring is not performing well. The main performance concern with database mirroring is whether the log records are processed in a timely manner. For more information about monitoring database mirroring, you can take a look at the article Monitoring Mirroring Status at msdn2.microsoft.com/fr-fr/library/ms365781.aspx.

Thanks to the following Microsoft IT Pros for their technical expertise: Chad Boyd, Sandu Chirica, Alan Doby, Kaloian Manassiev, Luciano Moreira, Ivan Penkov, Sivagaminathan Rajarethinam, Deborah To, Patrick Woodward, Buck Woody, Stanley Yau, and Warren Young.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.