Systems Engineering and RDBMS

Archive for the ‘Windows’ Category

MaxTokenSize and Windows Authentication

Posted by decipherinfosys on August 27, 2008

As you know already, there are two modes of connecting to SQL Server – Windows Authentication and SQL Server Authentication. When using Windows Authentication, if you have a large company with a lot of users and groups in the AD (Active Directory), at times you can see connectivity errors related to kerberos which look like this in the event log:

The kerberos SSPI package generated an output token of size 23C9 bytes, which was too large to fit in the 2349 buffer provided by the process id 0. If the condition persists, please contact your system administrator.

Similar error is logged into the SQL Server Error Log as well:

The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.

These kind of errors typically occur when users have memberships in many AD groups (this includes explicit as well as inherited memberships). There is a registry setting called MaxTokenSize which has a default value of 12000 decimal. In larger organizations, this default value is not adequate and the user tokens can be larger than this value. Since kerberos does not accept broken tokens, the authentication will fail because the value for that registry entry is not high enough.

So, how can you see what value you have in your environment for a given user? MSFT has a utility called TokenSZ which can be used to ascertain that. When you run it, you can see the default size for the MaxToken parameter. If you want to change the value on the server, you will need to modify (or add if it does not exist already) this registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters

Under it, you will either see the entry MaxTokenSize or if you do not see it in there, you can add one by right clicking in the right pane and selecting New/DWORD Value. Do that as shown in the image below and put in a higher value:

You will need to reboot in order for the entry change to take effect. Any server or a workstation that interacts with SQL Server will need this change. Some more good reading materials on this topic can be seen here and here.

Posted in SQL Server, Windows | Leave a Comment »

Hosting Environment – Database Servers

Posted by decipherinfosys on August 26, 2008

For one of our clients, we had helped them with their implementation in the production environment. The hosting provider had asked for a clear understanding of who will do what and what kind of responsibilities will be shared between the client production team and the hosting provider. We thought our readers might benefit from this list as well. In this case, the client production team had a good team of DBAs/Database Developers and so the entire monitoring and parts of the maintenance work was done by that team rather than the hosting provider team. Here is the spreadsheet.

Topic Hosting Provider Client
Database Server Set-up
SAN/RAID array configuration and hardware set-up X Instructions/Requirements from Client
Windows 2003 64-bit Enterprise Edition + SP2 X
Set up of the logical volumes X Instructions/Requirements from Client
SQL Server 2005 Enterprise Edition 64-bit install + SP2 install + Cumulative Update #3 X Instructions/Requirements from Client
Cluster set up for local failover X
Log Shipping for site failover X Instructions/Requirements from Client
Disaster Recovery Tests and runbook for disaster recovery X
Instance and DB Configuration
Instance and DB configuration parameters X
Login accounts and permissions X
DB Creation X
Schema object creation X
Startup parameters X
Trace Flag configuration X
Maintenance
Back-ups (Configuring and verifying via restores from time to time) X Instructions/Requirements from Client
Stats collection X
Fragmentation monitoring and corrective action scripts X
Integrity Checks X
Proper Filesizes configuration X
System/Services re-cycling (if at all necessary) X After confirmation from Client
Monitoring
Wait Events and Queues Troubleshooting Scripts X
Perfmon templates X
Profiler templates X
User defined Alert scripts configuration(severity errors, disk space, cpu/memory/io thresholds etc.) X
Baseline performance analysis and configuration (Trend Analysis) X
Event logs and SQL Error Logs monitoring X
Auditing – System and Instance X
SSIS and other packages configuration X
Cluster monitoring, mirroring and hardware issues monitoring X
Upgrades
Application DB Version Upgrades/Migrations X
Application DB patches X
Critical DB/application fixes X
Patches
OS patches X After confirmation from Client
Security Patches (OS or SQL) X After confirmation from Client
SQL Server patches X After confirmation from Client
Remote Logins
Administrator account password X X
Another user with admin privs. X X
sa password X X

Posted in Performance Tuning, SQL Server, Windows | 1 Comment »

VMWare now has MSFT validation in SVVP

Posted by decipherinfosys on August 22, 2008

Read on one of the blog posts here that VMWare has now joined MSFT’s Server Virtualization Validation Program (SVVP). VMWare is added to the SVVP web site as well. So, what does it mean to us? It means that unlike before, MSFT will now provide support for a number of it’s server operating systems when they are running in a VMWare environment. This is the list of the supported operating Systems: Windows Server 2008, Windows Server 2003 + SP2 and above, Windows 2000 Server + SP4.

Posted in VMWare, Windows | Leave a Comment »

More goodies from Sysinternals

Posted by decipherinfosys on August 21, 2008

AccessChk v 4.2 and AutoRuns for Windows v9.32 are the latest utilities from Sysinternals.  Both are geared towards sysadmins and are a good addition to the toolset for a SE.

Posted in Windows | Leave a Comment »

SQL Server 2005 P2P Replication

Posted by decipherinfosys on August 20, 2008

We had covered P2P replication before in one of our posts over here.  Here is a link to the technet post which talks about how MSFT has implemented it in house:

http://technet.microsoft.com/en-us/magazine/cc160974.aspx

Posted in Load Balancing, SQL Server, Windows | Leave a Comment »

More on Virtualization

Posted by decipherinfosys on August 19, 2008

Microsoft is pushing virtualization really hard nowadays.  The common questions that come to mind (besides learning about virtualization) are: What will be the cost?  What about managing such an environment?  And is the change really worth it?  If you are a Microsoft shop, then using the tools and technologies that you are already familiar with, moving towards virtualization is the right thing to do.  You can read more on the end to end capabilities of the MSFT virtualization solution over here.  We are setting up a lab in our office to benchmark our application using MSFT virtualization environment and will post the results on the blog.

Posted in Technology, Windows | Leave a Comment »

Hyper-V is available now

Posted by decipherinfosys on July 15, 2008

As you know, a beta version of Hyper-V was included with the Windows 2008 release. It’s RTM (Released to Manufacturing) version was been released and it is ready for deployments. You can read more over here as well as download it from that link. And if you are new to Hyper-V, this MSDN post will be a good point to start from.

Posted in Windows | Leave a Comment »

Virtualization Wars

Posted by decipherinfosys on July 11, 2008

We have covered virtualization before at our blog but mostly VMWare and a couple of posts on Virtual Server as well. With Windows Server 2008′s Hyper-V, it looks like it will challenge the market even more for ESX Server 3.5. With hardware changes coming in way faster than the software changes, the servers age out faster due to hardware issues rather than the OS. With virtualization, a physical server can easily be migrated into a virtual environment with the same applications and then moved on to a new physical hardware. We have clients who are using virtualization now even in their production environments let alone the development and QA environments.

If you are not already using virtualization in your environments, now would be the time to start getting serious about it. With Hyper-V, the technology moves the virtualization layer directly against the hardware. Hyper-V allows virtualization of both 32 bit as well as 64 bit architectures. MSFT has extended their virtualization platform offering by providing Microsoft App. virtualization (this was known as SoftGrid before) and desktop virtualization. not only that, one can use the System Center Virtual Machine Manager to do centralized management of a virtual set up.

Not only are these choices cost effective by providing us with consolidation and DR/HA (Disaster Recovery and High Availability) choices, it also eases up maintenance work for System Engineers as well as DBAs. An added benefit of consolidation is reduced electricity costs :-)

Both ESX Server 3.5 and Hyper-V are based on hypervisor based architectures which are better than the previous hosted virtualization technologies. In the case of the hosted virtualization products, one had to run the virtualization software on top of the OS of the machine and that adds a lot of overhead and not only that, it has a longer code execution path for the VMs. Hypervisor based architectures run the hypervisor directly on the hardware which means that there is no OS between the hypervisor and the system hardware. Even though both ESX Server 3.5 and Hyper-V as both based on hypervisor base architectures, there are a good bit of differences between them. We are currently doing some benchmarks with ESX Server 3.5 as well as Hyper-V and will post the results of the tests at our blog as and when they become available and we consolidate them. Till then, you can read more on these over here:

ESX Server 3.5

Hyper-V and the FAQs

Posted in Hyper-V, Virtual Server, VMWare, Windows | 1 Comment »

SQL Server on Windows 2008

Posted by decipherinfosys on July 9, 2008

Here is an excellent post from the folks at Microsoft on what needs to be done in order to install SQL Server (2005 or 2008 version) on Windows 2008…an excellent read:

http://blogs.msdn.com/sqlsecurity/archive/2008/07/01/sql-server-and-the-windows-server-2008-firewall.aspx

Posted in SQL Server, Windows | Leave a Comment »

Performance Counters are missing

Posted by decipherinfosys on June 26, 2008

In yesterday’s post, we had discussed how to go about uploading the perfmon data into a database for querying the data using simple SQL scripts. In this post, we will cover the scenarios when you see that a lot of your performance counters are missing. This question was asked by one of our readers and is also a very common situation in many different shops. We have seen this happen when there are clustered servers or when counters get updated by a hot fix or a service pack.

If you look into the Windows resource kit, you will find a utility called CTRLIST.exe. This reports on all the objects and the counters that are loaded on a particular server. There is also a GUI version of the utility available and the reason why we would recommend using either the command line version or the GUI version is to be able to look at the specifics of the DLLs that are related to each of those counters. That way, it becomes easier to troubleshoot in case a counter is missing or is not working for some reason. You can download the Windows Resource Kit from here.

After reviewing the output from the GUI utility or the text file from the command line utility, you can then check the Windows Application and System logs to see if there are any warnings or errors related to the loading of the performance library. If nothing specific is noticed, we would recommend just unloading and reloading the counter, example:

If the SQL Server counters are missing in action:

a) From the output from the GUI/text file from the command line utility, make a note of the sqlctr.ini file location.

b) Unload the SQL Server counters by executing:

Unlodctr mssqlserver

c) Reload the counters:

Lodctr C:\Program Files\SQL\MSSQL.1\MSSQL\Binn\sqlctr.ini

If you are using a clustered instance, then you need to use the Virtual Server Name in the unload and reload commands and if you have a named instance, then you need to use the virtual server name. If that also does not work for you, look up this KB article from MSFT that talks about rebuilding the entire performance counter library. Please do all this on a test system first to become comfortable with the steps.

Posted in SQL Server, Windows | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers