Rebuilding Master-Caching Issue

I recently encounterd an issue where I had to change the server levl collation settings and I decided to go with rebuilding master database.Itried to run this command line utility for SQL Server 2005.

start /wait \setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION= SQL_Latin1_General_CP437_CS_AS

I recived an error stating the "Installation package for the product SQL Server 2005 (64bit) cannot be found. Please locate a valid SQLRUN_SQL.MSI"

I tried to find out the resolution for this and after searching for around 2 hours I got the short and painless solution for this issue.I added ‘REINSTALLMODE=vomus’ which instructs setup to ignore the cached location of the .MSI file and use the local(specified) one.

start /wait \setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION= SQL_Latin1_General_CP437_CS_AS REINSTALLMODE=vomus

How to fix orphaned SQL Server users

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. There are three possible ways to make a user orphaned

• Explicitly drop the login that user belongs to
• Restore database on another instance of sql server.
• Restore of an old master backup


Use database admin privilage account for resolving this issue.Find out the list of orphaned users for the database using below SP:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

As per BOL ,Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended. You can use Update_One which Links the specified user in the current database to login. login must already exist. user and login must be specified.

EXEC sp_change_users_login 'update_one', ‘UserName’,'LoginName'

SQL Server- Basics

The smallest unit of data that SQL Server works with is 8KB. This 8KB chunk of data is called pages. SQL Server therefore stores data on the disk in pages. In Memory also SQL Server manipulates those same 8 KB chunks (pages).

An entire record or row must fit within 8kb chunk or page when data is written to the disk. This means that sometime a single page can share more than one row. A row cannot span multiple pages.E.g. If a employee table has employee name, address, city, state, zip, phone then all that combined data must be written in less than a page (or 8KB). Few data types like LOB of text, binary data like files which stores pointer to the real data can be spread across multiple pages or even in file. All this data is gathered in to the data files on the disk which has either .MDF or .NDF as file extension.

When a Structured Query Language (SQL) is written in the query analyze of SQL Server then SQL Server's internal query optimizer looks at the query and constructs a execution plan for executing it (i.e. what all steps it will need to take in order to get that data off of the disk). SQL Server has a number of techniques it can use, some which are better in certain conditions than others. Once SQL Server has the plan, it executes it and retrieves the needed data off of the disk. If the data is requested from the client then the data is sent across the network.

In case of any modification query (DML), SQL Server first modifies the pages of that data in the memory. After modification it does not write those updated pages directly to the disk, but it makes a copy of the DML query result in a log file called transaction log. A transaction log file has .LDF extension. It keeps track of every transaction in the database. If it writes to the disk after every DML query is fired on the table then it would increase the system load to write records on to the disk.

SQL Server has an automated recovery mode that kicks in when it starts back up after a SQL Server crash. Once the server is up after the crash, it will decide to write the modified pages to the disk or not. It first checks in the transaction log file if the transaction is completed then it goes and writes to the disk. This is how SQL Server knows that the change is safe on the disk.

When SQL Server crashed, it has automated recovery mode that gets kick when SQL Server starts back up. It first go to the transaction log and checks for the uncommitted transaction or those which are not yet flagged. It knows that the flagged transactions are safe on the disk and the remaining was not copied to disk but still present in the memory when it crashed. In this case SQL Server reads those transactions from the log file, executes them again and immediately writes/commit those pages to the disk. This process allows SQL Server to catch up with all in-progress work and ensures that no loss of data occurred (provided database files are not corrupted). In short whatever happens in SQL Server it takes place through transaction log. Again it depends on the recovery model of the database. We have these 3 database recovery model – Full, Simple and Bulk-logged.

Individual databases can be changed from one recovery model to other depending upon the requirement or functionality. E.g. in Simple recovery model does not use transaction log (very less usage). It removes the transactions automatically to keep the log file size to minimal. Simple recovery is appropriate for read-only database that have no changes being made. If there is no change then there will not be any data loss during SQL Server crash.

This is how the data moves from disk to memory. This entire process is absolutely essential to how most of SQL Server's functionality actually works, as well as how to administer it.

Troubleshoot Suspect Database

Sometimes we encounter problem that the database is in suspect mode and cannot be recovered if we do not have the latest backup of the database. This may take place when the database is not closed properly before SQL Server shutdown. Sometimes the DBA try to detach the suspect database and try to attach the data and log file of the suspect database. It gives following error:

“The log scan number (number:number:number) passed to log scan in database 'database_name' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.”

Sometimes we also try to repair the Suspect database using run the DBCC CHECKDB command with the REPAIR_REBUILD, but it fails with the following error:

“Database 'databasename' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server error log for more information.”

Following is the solution to resolve this issue:

If the DBA has detached the database try following steps:
1.Detach the suspect database.
2.Rename the existing “.ldf” to “_old.ldf” file
3.Use following attach command to attach the database which will create new log file.

CREATE DATABASE database_name
ON [ ,...n ]
FOR ATTACH_REBUILD_LOG

If DBA has not detached the database then

1.Set database to emergency mode by using following command

ALTER DATABASE SET EMERGENCY

Use Master
GO
ALTER DATABASE SET SINGLE_USER
GO
DBCC CHECKDB(database_name, repair_allow_data_loss)

ALTER DATABASE SET MULTI_USER

ALTER DATABASE SET ONLINE

The database will be back online.

My SQL Server Instance is not visible from another Machine

This has happened because somebody has changed the settings for the SQL Server Instance.To rectify this issue,Login into the Server Hosting the instance.In my case the instance name is 'Instance1'.

Open SQL Server Configuration Manager



Right Click on the instance name which is not visible while connecting.



Select ‘Properties’->Check if the option ‘Hide Instance’ is enabled.If yes then change it to ‘No’.



You will receive a confirmation and instruction that this change will take effect once you restart the MSSQL Service.



Restart MSSQL service.

Authentication mode: Change from Registry

SQL Server can operate in one of two security (authentication) modes:

•Windows Authentication Mode (Windows Authentication)
•Mixed Mode (Windows Authentication and SQL Server Authentication

We can set the Authentication mode during installation of SQL Server database the authentication mode can be selected as SQL Server and Windows Authentication (Mixed) mode or Only Windows Authentication.

But what if we want the authentication mode to be changed after installation ??

Here is the solution for this problem,we can change the authentication mode from Windows Registery.Below are the steps.

i)Open the Registry Editor ( run –> regedit.exe)

ii)Navigate to the desired folder
a) For SQL Server 2000, navigate to the folder
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer
b) For SQL Server 2005, navigate to the folder
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
c) For SQL Server 2008, navigate to the folder
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\MSSQLServer

iii)Double click to open the LoginMode of Type REG_DWORD



iv)Enter the desired value
a) Enter the value 2 for SQL Server and Windows Authentication (Mixed) mode
b) Enter the value 1 for Windows Authentication mode.

V) Confirm by Clicking 'Ok'.

How to check contents of a backup file

RESTORE FILELISTONLY
The RESTORE FILELISTONLY option allows you to see a list of the all the files of the database which was backed up.
SSMS do not have this facility,unless you use SSMS for Restore.You can only get information using T-SQL

Restore FILELISTONLY from disk='C:\Prakash_Test.BAK' WITH FILE=1

The RESTORE FILELISTONLY option can be simply issued as follows for a backup that exists on disk.

If there are multiple backups in one backup file then you will have to specify the option "WITH FILE = (Number)" .If you don’t then you will only get information for the first backup in the file.

So for example if you have a full backup you will see all of the data files (mdf,ndf) and the log file (ldf).

RAID Levels

Redundant array of inexpensive disks (RAID) is used to configure a disk subsystem to provide better performance and fault tolerance for an application. The basic idea behind using RAID is that you spread data across multiple disk drives so that I/Os are spread across multiple drives. RAID has special significance for database-related applications, where you want to spread random I/Os (data changes) and sequential I/Os (for the transaction log) across different disk subsystems to minimize disk head movement and maximize I/O performance.
Since multiple disks increases the mean time between failures (MTBF), storing data redundantly also increases fault tolerance. A RAID appears to the operating system to be a single logical hard disk. RAID employs the technique of disk striping, which involves partitioning each drive's storage space into units ranging from a sector (512 bytes) up to several megabytes.
RAID levels

RAID-0
RAID-0 uses disk striping; that is, it writes data across multiple hard disk partitions in what is called A stripe set. This can greatly improve speed because multiple hard disks are working at the same time. You can implement RAID-0 through the use of Windows Server software or third-party hardware. Although RAID-0 gives you the best speed, it does not provide any fault-tolerance. If one of the hard disks in the stripe set is damaged, you lose all of your data. Because of the lack of faulttolerance, Microsoft doesn’t recommend storing any of your SQL Server data on RAID-0 volumes.

RAID-1
RAID-1 uses disk mirroring. Disk mirroring actually writes your information to disk twice — once to the primary file and once to the mirror. This gives you excellent fault-tolerance, but it is fairly slow, because you must write to disk twice. Windows Server allows you to implement RAID-1 with a single controller, or you can use a controller for each drive in the mirror, commonly referred to as disk duplexing. This is the recommended place for storing your transaction logs because RAID-1 gives fast sequential write speed (writing data in sequence on the disk rather than jumping from one empty spot to the next), a requirement for transaction logs.

RAID-5
RAID-5—striping with parity — writes data to the hard disk in stripe sets. Parity checksums will be written across all disks in the stripe set. This gives you excellent fault-tolerance as well as excellent speed with a reasonable amount of overhead. You can use the parity checksums to re-create information lost if a single disk in the stripe set fails. If more than one disk in the stripe set fails, however, you will lose all your data. Although Windows Server supports RAID-5 in a software implementation, a hardware implementation is faster and more reliable, and we suggest you use it if you can afford it. Microsoft recommends storing your data files on this type of RAID because data files require fast read speed as opposed to transaction logs, which need fast write speed.

RAID-10
You should use RAID-10 (sometimes referred to as RAID 0+1) in mission-critical systems that require 24/7 uptime and the fastest possible access. RAID-10 implements striping with parity as in RAID-5 and then mirrors the stripe sets. So, you get the incredible speed and faulttolerance, but RAID-10 has a drawback. With this type of RAID you get the added expense of using more than twice the disk space of RAID-1. Then again, we are talking about a situation that can afford no SQL Server downtime
Unless you can afford a RAID-10 array, Microsoft suggests a combination of RAID-5 and RAID-1. In this scenario, you place your data files on the RAID-5 array for speed and redundancy. You place your transaction log files on the RAID-1 drives so they can be mirrored.

How to Setup Peer-to-Peer Transactional Replication

General concepts
Replication is an important technology that can be used in SQL Server. With replication, a DBA can synchronize data between two or more databases. This vital function is primarily used when a DBA needs to share server loads to improve database server performance. Replication can also help to provide high availability to TCP/IP services.

As you may be aware, SQL Server 2000 provides three types of replication:
• Snapshot replication.
• Transactional replication.
• Merge replication.

SQL Server 2005 includes a new replication method:
• Peer-to-peer transactional replication.

This article describes this new replication method.

SQL Server 2005 Editions

The different editions of previous versions of SQL Server, except SQL Server CE, all supported each of the different types of replication. This is no longer true with SQL Server 2005.




Table 1: Replication types by SQL Server edition. (Source: Books Online)

Non-SQL Server subscribers, Oracle publishing, and peer-to-peer transactional replication are new features of SQL Server 2005, but they are not available in all editions. As the table above indicates, peer-to-peer transactional replication is only available in enterprise and developer editions of SQL Server 2005.
The developer edition of SQL Server includes all of the functionality of the enterprise edition; however, it is licensed for use as a development and testing server, not for use as a production server. Therefore, you will need the enterprise edition to take advantage of peer-to-peer replication. The retail price of a processor license for SQL Server enterprise edition is about four times that of the standard edition. Obviously, cost considerations will be a factor.

Using Peer-To-Peer Transactional Replication

Let's consider a typical online e-commerce application. In order to avoid downtime and reduce the load on any single server, the database for this application is in more than one location. As it is an online e-commerce system, data needs to be changed (through inserts, updates, and deletes) at each location and all the data modifications need to be replicated to the other servers. For example, let's assume there are databases at location A, B, and C.

To accomplish this in SQL Server 2000, we would use merge replication. Location A is the publisher, and B and C are the subscribers.

How to Configure Peer-to-Peer Replication

Now that we have seen how peer-to-peer transactional replication works, let's look at the configuration options.

First, we need to create a publication. In SQL Server 2005, we'll use the New Publication Wizard.



Figure 3.1: New Publication Wizard.



Figure 3.2: New Publication Wizard.

As you can see (above), there are four publication types. To configure peer-to-peer transactional replication, we need to select Transactional publication.
After the publication is created, we need to change its properties. In the Publication Properties window (see below), set Allow peer-to-peer subscriptions to True. Please note that once we set this property to "true" we cannot revert to "false" until replication for the publication is dropped.



Figure 4: Publication Properties.

Now we'll go back, right click on the publication, and run the peer-to-peer transactional replication wizard (see below).

With this wizard, we can add more SQL Servers or SQL Server instances to the peer network. You will not be able to add main publication databases or the previously selected database on which we ran the wizard.


Figure 5.1: Configure Peer-To-Peer Topology Wizard: Topology Wizard.


Figure 5.2: Configure Peer-To-Peer Topology Wizard: selecting peers.

After configuring the peer network, we need to specify how we have initialized the databases for the other servers in the peer-to-peer network.


Figure 6: Configure Peer-To-Peer Topology Wizard: initializing the new peers.

The obvious way to initialize the new peer databases is to restore the backups (making sure they are not changed before setting up peer-to-peer replication). Otherwise, we can specify the backup file used. The SQL Server engine will synchronize the databases by comparing the publication database to the backup.


Figure 1: Merge replication.

The obvious drawback of this method is that it is a single point of failure configuration. If database B is down, the A to C replication link will continue to work. Users who are connected to both servers A and C will not see any adverse effect. However, if database A fails, B and C will be isolated from the system. Modifications will not be visible on other end.
SQL Server 2000 replication uses the publisher and subscriber hierarchy method. Successful operation of this configuration requires that the publisher be present at all times.

With the peer-to-peer replication topology in SQL Server 2005, each node acts as a publisher and as a subscriber. Replication recognizes when changes have occurred on a given node but only allows those changes to cycle through the nodes one at a time.

Figure 2: Peer-to-peer replication.

If one database is down (A, for example), the other databases (B and C) can still replicate. Whenever that database (A) comes back up, it can synchronize with the others (B and C) and get the changes that took place after it went down. This is possible because the databases (A, B, and C) all act as both publisher and subscriber.
After we provide authentication for the Log Reader and the SQL Agent, the wizard will begin building the peer-to-peer topology. For each publication, the wizard will create a distribution database as well.

When we have completed the wizard, this is what we'll see in SQL Server 2005 explorer.

Introducing Another Node

In today's competitive environment, businesses expand widely and frequently. What if we need to add another node? In SQL Server 2005, using peer-to-peer replication, all we need to do is configure the new node as a publisher and a subscriber and attach it to the peer-to-peer network.

Conclusion
Peer-to-peer transactional replication has added a new dimension to SQL Server database replication, giving DBAs more flexibility. As a new feature, it isn't yet widely used. But it will be. So check it out and see how your organization will benefit.

Server running with High CPU Utilization

 Check the status of CPU by opening Task Manager


 Check and identify the process which takes more memory (find out PID – process ID)

Click on View->select columns-> there select PID (Process identifier)

 Check if the respective process is related to any program which is running in the back ground and if not required then kill it by PID (Command kill pid no.). For example if we want to kill pid 100, then the associated command would be - kill 100

 Check for the event viewer for any errors generated recently which might cause the issue

 Also we can check the Page memory/virtual memory ration. It should not be above 4.

 Ration: For CPU Utilization the ratio should be below 80% [As per the counter set in the performance monitoring tool]

 Some times svchost consumes more resource, so do not kill that process as it contains some other sub process related to windows and with the same naming convention.

 Note: Also we cannot kill the process system generated

 If we find any unknown process in the process tab in task manager then search it in the google for the detailed information. Sometimes there may be a chance of server getting infected and there are several processes running with some unknown name. So we need to identify it and uninstall the related program from the systems along with the files associated with it.

 Hotfixes/service packs: Some times because of missing patches/hot fixes the server runs with 100% cpu usage. For example: You experience slow performance and high CPU utilization on a Microsoft Windows Server 2003 Terminal Server. On a single-processor server, CPU utilization may reach 100%.



In addition, the Remote Desktop Connection window on a terminal client stops responding.

Cause: This problem occurs because the Win32k.sys driver does not correctly verify some data before the data is used. A supported hot fix is available from Microsoft. However, this hot fix is intended to correct only the problem that is described in this article. Apply this hot fix only to systems that are experiencing this specific problem. This hot fix might receive additional testing. Therefore, if you are not severely affected by this problem, it is recommend that you wait for the next software update that contains this hot fix.



There are tool available which helps to identify the process tree/sub process and resolve the issue.

 Process explorer is the simple exe which we can download and run in to the system. It is used to explore the process in to the nth level and show the sub process and their status relatively. By using this we can easily identify the process/program and kill them if required.

 Some times because of some unwanted services running in back ground cpu experience slowness, so we need to identify it and stop it and check the status.

 We can set a perticuler counter in the performance monitoring tool to identify the process consuming more resource.

 Here the troubleshooting steps vary based on the process/service using more cpu. Based on the service/process name we need to proceed with the troubleshooting steps.