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.