SQL Server and Networking

How do you test proper TCP/IP configuration Windows machine?

Windows NT: IPCONFIG/ALL, Windows 95: WINIPCFG, Ping or ping ip.add.re.ss

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.

How do you define testing of network layers?

Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.

What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

How many layers of TCP/IP protocol combined of?

Five. (Application, Transport, Internet, Data link, Physical).

How many bits IP Address consist of?

An IP Address is a 32-bit number.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs.

What is the difference between them (Ethernet networks and token ring networks)?

With Ethernet, any devices on the network can send data in a packet to any location on the network at any time. With Token Ring, data is transmitted in ‘tokens’ from computer to computer in a ring or star configuration. Token ring speed is 4/16 Mbit/sec , Ethernet - 10/100 Mbit/sec.

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.

What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What is a traditional Network Library for SQL Servers?

Named Pipes.

Table Locking enhancement - SQL Server 2008

In SQL Server 2008 ALTER TABLE statement, you can find a new option called SET LOCK_ESCALATION. This is one of the enhancements in Locking in SQL Server 2008. This option can have three value, Auto, Table, Disable

FROM BOL
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Specifies the allowed methods of lock escalation for a table.

AUTO
This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.
• If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.
• If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

TABLE
Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

DISABLE
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

Note : If you use partitions then After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table.

Resource Governor in SQL Server 2008

Resource Governor in SQL Server 2008

SQL Server 2008 Resource Governor allows you to control the resource according to the requirements. This was motive behind this feature is providing predictable response to the user in any situation. In earlier versions, we had a single pool of resources like Memory, CPU, threads etc. You can not priorities the workload versus Resource pool in 2005 and earlier version. Generally, who accesses the system first and starts a process, it can consume the resources without any restrictions. Consider, some kind of BI runaway query is first hit system where the OLTP and OLAP Databases are in the same server. Now the OLTP process has to wait till the OLAP process releases the resource. This was a major concern in earlier versions. So what were the solution then, go for multiple instances and configure the Resource per instance or go for different machine altogether. Both methods were having their own problem. By specifying the resource, if the system is not using that resource still will not released. If you go for another machine, you may have license issue and it’s not a cost effective method.

In SQL Server 2008, these problems are addressed by providing a tool called Resource Governor. You can differentiate the workload by Application Name, Login, Group, by database name etc. Once you have defined the workload, you can configure the resource which can consumed by workload. Probably, you want to give more resource for your OLTP application than the OLAP. You have that kind of flexibility and control here.
The following three concepts are fundamental to understanding and using Resource Governor:
• Resource pools: Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
• Workload groups: Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
• Classification: There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

SQL Server 2008 Sparse column

SQL Server 2008 Sparse column

One of the major enhancements in database engine of SQL Server 2008 is Sparse column. It improves data retrieval and reduces the storage cost. It also can be used with Filtered Index to improve the performance.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

How to create sparse column?
Simple , just mention sparse keyword in table creation or alter statement.

CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)

Data Page LSN and Transaction Log LSN

Data Pages

The SQL Server database page size is 8 KB. Each page contains a header with fields such as Page Number, Object Id, LSN, Index Id, Torn bits, and Types. The actual row data is located on the remaining portion of the page. The internal database structures track the allocation state of the data pages in the database.

Data pages are also referred to as pages.

The Microsoft SQL Server 2000 transaction log operates logically as if it is a serial string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN higher than the LSN of the record before it.

Log Shipping Requirements

Log Shipping Requirements

Log shipping has the following requirements:
* SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
* The servers involved in log shipping should have the same case-sensitivity settings.
* The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

Permissions
You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.
* For the backup job, read/write permissions to the backup directory are required on the following:
o The SQL Server service account on the primary server instance.
o The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
* For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
* For the restore job, read/write permission to the copy directory are required by the following:
o The SQL Server service account on the secondary server instance.
o The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

Network Troubleshooting Dos Commands

Network Troubleshooting Dos Commands

PING
Ping is the most important troubleshooting command and it checks the connectivity with the other computers. For example your system’s IP address is 10.10.10.10 and your network servers’ IP address is 10.10.10.1 and you can check the connectivity with the server by using the Ping command in following format.

At DOS prompt type Ping 10.10.10.1 and press enter

If you get the reply from the server then the connectivity is ok and if you get the error message like this “Request time out” this means the there is some problem in the connectivity with the server.

IPCONFIG
IPconfig is another important command in Windows. It shows the IP address of the computer and also it shows the DNS, DHCP, Gateway addresses of the network and subnet mask.

At DOS prompt type ipconfig and press enter to see the IP address of your computer.

At DOS prompt type inconfig/all and press enter to see the detailed information.

NSLOOKUP
NSLOOKUP is a TCP/IP based command and it checks domain name aliases, DNS records, operating system information by sending query to the Internet Domain Name Servers. You can resolve the errors with the DNS of your network server

HOSTNAME
Hostname command shows you the computer name.

At DOS prompt type Hostname and press enter

NETSTAT
NETSTAT utility shows the protocols statistics and the current established TCP/IP connections in the computer.

NBTSTAT
NBTSTAT helps to troubleshoot the NETBIOS name resolutions problems.

ARP
ARP displays and modifies IP to Physical address translation table that is used by the ARP protocols.

FINGER
Finger command is used to retrieve the information about a user on a network.

TRACERT
Tracert command is used to determine the path of the remote system. This tool also provides the number of hops and the IP address of each hop. For example if you want to see that how many hops (routers) are involved to reach any URL and what’s the IP address of each hop then use the following command.

At command prompt type tracert www.yahoo.com you will see a list of all the hops and their IP addresses.

TRACEROUTE
Traceroute is a very useful network debugging command and it is used in locating the server that is slowing down the transmission on the internet and it also shows the route between the two systems

ROUTE
Route command allows you to make manual entries in the routing table.

Networking Questions

What is DNS?

Domain name system/server is used to translate the IP address into the hostname and hostname into the IP address. DNS is mostly used on the internet and the networks.

What is DHCP?

Dynamic host configuration protocol is used to dynamically assign the IP address to the networked computers and devices. DHCP is a network protocol that automatically assigns static and dynamic IP addresses from its own range.

What is a Router?

Router is the most important network device that is used to connect two logically and physically different networks. Router defines the short possible route for the data to reach its destination. A router works with built-in intelligent software known as routing table, which helps to determine the route between the two networks.

What is Gateway?

A gateway is software or a hardware that is used to connect the local area network with the internet. A gateway is a network entrance point and a router usually works as a gateway.

What is WLAN?

WLAN or Wireless local area network is simply a type of network that doesn’t use wired Ethernet connections for networking. WLAN uses wireless network devices such as wireless routers etc.

What is Subnet Mask?

A subnet mask is used to determine the number of networks and the number of host computers. Every class of the IP address uses the different range of the subnet mask. Subnet masks allow the IP based networks to be divided into the sub networks for performance and security purposes.

What is a MAC Address?

MAC address or Media Access control is a unique identifier of a computer device. The MAC address is provided by the manufacturer of the device. MAC addresses are 12 digital hexadecimal numbers.

What is an IP Address?

An IP address is a unique identifier of a computer or network device on the local area network, WAN or on internet. Every host computer on the internet must have a unique IP address. IP addresses on the internet are usually assigned by the local ISPs to which users are connected.

What is Wifi?

Wi Fi or wireless fidelity is a base band network technology that is used for the wireless data communication.

What is WiMax?

Wi Fi is a next form of the Wi fi. Wi max is a very high speed broadband network technology that is designed for the corporate offices, roaming and home users.

Name the Seven Layers of OSI Model

The seven layers of the OSI are Application, Presentation, Sessions, Transport, Network, Data Link and Physical layer.

What is LDAP?

Lightweight Directory Access Protocol is used to access the directory services from the Active directory in Windows operation systems.

What are the standard port numbers for SMTP, POP3, IMAP4, RPC, LDAP, and FTP?

SMTP – 25, POP3 – 110, IMAP4 – 143, RPC – 135, LDAP, FTP-21, HTTP-80

What is IPv6?

IP V6 is a next generation protocol that is used as an expansion of DNS.

What is UDP?

UDP or user datagram protocol is a connectionless protocol that is used to transfer the data without any error handling.

What is Firewall?

A firewall is usually a software program that is installed on the network server or gateway. The purpose of the firewall is to protect the network resources from the intruders and unauthorized persons.

What is Virtual Private Network (VPN) and how does it work?

VPN or virtual private network is used to connect two networks by means internet. VPN uses PPTP (point to point tunneling protocol) and other security procedures to make a secure tunnel on internet.

Define VOIP

VOIP or voice over internet protocol is a technology that uses IP based networks such as internet or private networks to transmit the voice communication.

Define Bluetooth Technology

Bluetooth is a short range wireless technology that uses radio waves for communication. Many mobile phones, laptops, MP3 players have built in features of the Bluetooth.

What is a RAS server?

RAS or remote access server allows you to remote dial in through the desktop computers, laptops and GSM mobile phones.

What’s a Frame Relay?

Frame relay is high speed data communication technology that operates at the physical and data link layers of the OSI model. Frame relay uses frames for data transmission in a network.

Networking Interview Questions

What is IPv6?

Internet Protocol version 6 (IPv6) is a network layer IP standard used by electronic devices to exchange data across a packet-switched internetwork. It follows IPv4 as the second version of the Internet Protocol to be formally adopted for general use.  ipv6 it is a 128 bit size address. This is total 8 octants each octant size is 16 bits separated with “:”, it is in hexa decimal format. These 3 types:

1. unicast address


2. multicast address


3. anycast address

loopback address of ip v6 is ::1

What is subnet?

A subnet allows the flow of network traffic between hosts to be segregated based on a network configuration. By organizing hosts into logical groups, subnetting can improve network security and performance.

What is Subnet Mask?

A mask used to determine what subnet an IP address belongs to. An IP address has two components, the network address and the host address. For example, consider the IP address 150.215.017.009. Assuming this is part of a Class B network, the first two numbers (150.215) represent the Class B network address, and the second two numbers (017.009) identify a particular host on this network.

Subnetting enables the network administrator to further divide the host part of the address into two or more subnets. In this case, a part of the host address is reserved to identify the particular subnet. This is easier to see if we show the IP address in binary format.

What is Default Gateway?

a gateway is a device on a network that acts as an entrance to another network. In more technical terms, a gateway is a routing device that knows how to pass traffic between different subnets and networks. A computer will know some routes (a route is the address of each node a packet must go through on the Internet to reach a specific destination), but not the routes to every address on the Internet. It won't even know all the routes on the nearest subnets. A gateway will not have this information either, but will at least know the addresses of other gateways it can hand the traffic off to. Your default gateway is on the same subnet as your computer, and is the gateway your computer relies on when it doesn't know how to route traffic.

The default gateway is typically very similar to your IP address, in that many of the numbers may be the same. However, the default gateway is not your IP address.

Describe how the DHCP lease is obtained.
It’s a four-step process consisting of (a) IP request, (b) IP offer, © IP selection and (d) acknowledgement.

What’s the difference between forward lookup and reverse lookup in DNS?
Forward lookup is name-to-address, the reverse lookup is address-to-name.

How can you recover a file encrypted using EFS? Use the domain recovery agent.

What is LMHOSTS file?
It’s a file stored on a host machine that is used to resolve NetBIOS to specific IP addresses.

How can you force the client to give up the dhcp lease if you have access to the client PC?
ipconfig /release

Open Systems Interconnection Model

OSI 7 Layers Reference Model For Network Communication

Open Systems Interconnection (OSI) model is a reference model developed by ISO (International Organization for Standardization) in 1984, as a conceptual framework of standards for communication in the network across different equipment and applications by different vendors. It is now considered the primary architectural model for inter-computing and internetworking communications. Most of the network communication protocols used today have a structure based on the OSI model. The OSI model defines the communications process into 7 layers, which divides the tasks involved with moving information between networked computers into seven smaller, more manageable task groups. A task or group of tasks is then assigned to each of the seven OSI layers. Each layer is reasonably self-contained so that the tasks assigned to each layer can be implemented independently. This enables the solutions offered by one layer to be updated without adversely affecting the other layers.

The OSI 7 layers model has clear characteristics. Layers 7 through 4 deals with end to end communications between data source and destinations. Layers 3 to 1 deal with communications between network devices.

The specific description for each layer is as follows:

Layer 7:Application Layer

Defines interface to user processes for communication and data transfer in network

Provides standardized services such as virtual terminal, file and job transfer and operations

Layer 6:Presentation Layer

Masks the differences of data formats between dissimilar systems

Specifies architecture-independent data transfer format

Encodes and decodes data; Encrypts and decrypts data; Compresses and decompresses data

Layer 5:Session Layer

Manages user sessions and dialogues

Controls establishment and termination of logic links between users

Reports upper layer errors

Layer 4:Transport Layer

Manages end-to-end message delivery in network

Provides reliable and sequential packet delivery through error recovery and flow control mechanisms

Provides connectionless oriented packet delivery

Layer 3:Network Layer

Determines how data are transferred between network devices

Routes packets according to unique network device addresses

Provides flow and congestion control to prevent network resource depletion

Layer 2:Data Link Layer

Defines procedures for operating the communication links

Frames packets

Detects and corrects packets transmit errors

Layer 1:Physical Layer

Defines physical means of sending data over network devices

Interfaces between network medium and devices

Defines optical, electrical and mechanical characteristics

What is the Global.asax file?

What is the Global.asax file?

The Global.asax file (also known as the ASP.NET application file) is an optional file that is located in the application's root directory and is the ASP.NET counterpart of the Global.asa of ASP. This file exposes the application and session level events in ASP.NET and provides a gateway to all the application and the session level events in ASP.NET. This file can be used to implement the important application and session level events such as Application_Start, Application_End, Session_Start, Session_End, etc. This article provides an overview of the Global.asax file, the events stored in this file and how we can perform application wide tasks with the help of this file.

According to MSDN, "The Global.asax file, also known as the ASP.NET application file, is an optional file that contains code for responding to application-level events raised by ASP.NET." The Global.asax file is parsed and dynamically compiled by ASP.NET into a .NET Framework class the first time any resource or URL within its application namespace is activated or requested. Whenever the application is requested for the first time, the Global.asax file is parsed and compiled to a class that extends the HttpApplication class. When the Global.asax file changes, the framework reboots the application and the Application_OnStart event is fired once again when the next request comes in. Note that the Global.asax file does not need recompilation if no changes have been made to it. There can be only one Global.asax file per application and it should be located in the application's root directory only.

The following are some of the important events in the Global.asax file.

· Application_Init

· Application_Start

· Session_Start

· Application_BeginRequest

· Application_EndRequest

· Application_AuthenticateRequest

· Application_Error

· Session_End

· Application_End

The purpose of these event handlers is discussed in this section below.

Application_Init

The Application_Init event is fired when an application initializes the first time.

Application_Start

The Application_Start event is fired the first time when an application starts.

Session_Start

The Session_Start event is fired the first time when a user’s session is started. This typically contains for session initialization logic code.

Application_BeginRequest

The Application_BeginRequest event is fired each time a new request comes in.

Application_EndRequest

The Application_EndRequest event is fired when the application terminates.

Application_AuthenticateRequest

The Application_AuthenticateRequest event indicates that a request is ready to be authenticated. If you are using Forms Authentication, this event can be used to check for the user's roles and rights.

Application_Error

The Application_Error event is fired when an unhandled error occurs within the application.

Session_End

The Session_End Event is fired whenever a single user Session ends or times out.

Application_End

The Application_End event is last event of its kind that is fired when the application ends or times out. It typically contains application cleanup logic.

“Cannot Generate SSPI Context” error message, more comments for SQL Server

The “Cannot generate SSPI context” issue is described by http://support.microsoft.com/?id=811889 in general. In this post I will discuss one daunting case of “Cannot generate SSPI context” error message when failing to connect to SQL server. In most related cases, customers report this issue as "I can connect to my local SQL Server, but once I connect to my network, I can't connection to my local SQL Server". Such issue is reported against MSDE and SQLExpress. But actually, it can happen with any SKU of SQL Server, including SQL Server 2000 and SQL Server 2005, that support NT integrated authentication. The error message for the failed connection that we discussed here is

[SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context”
[MDAC] “Cannot generate SSPI context”;
[.Net1.0/2.0]” Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context”

It can happen when all of followings are true:
(1) The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
(2) The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
(3) The connection is to a local SQL Server.
(4) Connection configuration causes network library to choose TCP/IP provider.

A scenario that meets all of (1) (2) and (3) looks like an extreme corner case. But the reality is that it is quit often if the hosting machine is a laptop computer. One solution, of course, is to avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely. The reason why they work is subtle and I’ll discuss it later. From user’s perspective, however, in many cases, either connecting over VPN or disconnecting from network might prevent you from accessing some valuable resources, so I want to discuss solutions that do not depend on (1) first.

In most cases, users do not explicitly require TCP/IP as the connection provider. For example connection strings in form of “.\<instance>”, “(local)\<instance>”, “<servername>\<instancename>” are among them. Users might wonder why network library chooses TCP/IP provider instead of Shared Memory provider, if the connection string is not prefixed with “tcp” and the server is local. A simple answer is that it can happen if the TCP/IP provider is in front of other providers in the client protocol order list, or/and the local server is not listening on Share Memory and Name Pipe. As described above, only TCP/IP provider has the issue; hence, configuring network library not to choose TCP/IP is a solution. To do that, first, on the server side, make sure your server is listening on Shared Memory or/and Named Pipe connection requests; then, on the client side, change the protocol order list such that Shared Memory and/or Named Pipe are in front of TCP/IP, or prefixing your connection strings with “lpc” or “np” to force Shared Memory or Named Pipe, or using alias that prefix Named Pipe in connection strings, whichever you feel most comfortable with. Note that certain SKUs of SQL Server have named pipe connection turned off by default.

In very rare case, however, if you really in need of TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your <servername>. For example, if your connection string has form of “<servername>\<instancename>” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as <servername>\<instancenane>, protocol as TCP/IP, server as “127.0.0.1\<instancename>” or “127.0.0.1,<port>”. Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable. If the connection string is prefixed with “tcp”, then you do need to modify your connection string to specify “127.0.0.1” as <servername>.

If these workarounds described above do not fit your needs, we would like to hear more from you.

The reason that we didn’t fix this subtle issue is because the limitation is rooted in a behavior of an integrated authentication module (SPNEGO) in XP and windows 2000, i.e. whether to fallback to NTLM if KDC is not available when the target SPN points to local machine. KDC, normally, is part of your domain controller. For this specific case, SPNEGO chooses not to fallback, hence connection fail. This issue is not a security issue though. Reader might ponder why avoiding using TCP/IP provider can solve the problem while explaining it is because certain behavior of SPNEGO in Windows. Not going too deep, the simple answer is that only TCP/IP provider, with an exception of loop-back connection, uses SPNEGO while other providers use NTLM. Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

Back to the questions we left before, the reason that disconnected from network (no network media) works is because, in such case, local <servername> is resolved to “127.0.0.1” by windows network layer and NTLM is used directly. When connected over VPN, the SPNEGO issue goes away because the KDC is accessible in this case.

From the error message reported by SNAC ODBC/OLEDB, you can differentiated the issue described by this post from another case of “Cannot generate SSPI context”, in which the root cause is because, in Active Directory, the Service Principle Name (SPN) of SQL Server is registered for a domain account different from the SQL Server is actually running under. The error message for the other case is “[SQL Native Client]SQL Network Interfaces: The target principal name is incorrect.[SQL Native Client]Cannot generate SSPI context. The “Cannot generate SSPI context” issue is described by http://support.microsoft.com/?id=811889 in general

Important Backup Options

Important Backup options

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

The backup media is not overwritten if any one of the following conditions is met:

• All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.


• The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.

Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section.

Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.

NOINIT

Indicates that the backup set is appended to the specified disk or tape device, preserving existing backup sets. NOINIT is the default.

The FILE option of the RESTORE command is used to select the appropriate backup set at restore time

STANDBY = undo_file_name

Used only with BACKUP LOG. Backs up the tail of the log and leaves the database in read-only and standby mode. The undo file name specifies storage to hold rollback changes which must be undone if RESTORE LOG operations are to be subsequently applied.

If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it

NOUNLOAD

Specifies the tape is not unloaded automatically from the tape drive after a backup. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.

RESTART

Specifies that SQL Server restarts an interrupted backup operation. The RESTART option saves time because it restarts the backup operation at the point it was interrupted. To RESTART a specific backup operation, repeat the entire BACKUP statement and add the RESTART option. Using the RESTART option is not required but can save time.

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable

NO_TRUNCATE

Allows backing up the log in situations where the database is damaged.

How to take backup in Multiple files?

Problem
How to take backup in Multiple files (Or you can say how to split backup file in multiple backup files)?

Solution

Advantage:

1. We can store backup on multiple drive if disk space is the issue

2. Easy to transfer on network servers

T-SQL for Backup

BACKUP DATABASE [Northwind] TO

DISK = 'C:\Northwind_file1.bak',

DISK = 'D:\Northwind_file2.bak',

DISK = 'E:\Northwind_file3.bak'

WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

T-SQL for Restore

RESTORE DATABASE [northwind] FROM

DISK = N'C:\Northwind_file1.bak',

DISK = N'D:\Northwind_file2.bak',

DISK = N'E:\Northwind_file3.bak' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE

SQL Server Windows Authentication (MIX Mode Authenticatio)

SSPI (Security Support Provider Interface)

SSPI allows an application to use various security models available on a computer or network without changing the interface to the security system.

What is Kerberos?

Kerberos is a network authentication protocol. It is designed to provide strong authentication for client/server applications by using secret-key cryptography. A free implementation of this protocol is available from the Massachusetts Institute of Technology. Kerberos is available in many commercial products as well.

The Internet is an insecure place. Many of the protocols used in the Internet do not provide any security. Tools to "sniff" passwords off of the network are in common use by malicious hackers. Thus, applications which send an unencrypted password over the network are extremely vulnerable. Worse yet, other client/server applications rely on the client program to be "honest" about the identity of the user who is using it. Other applications rely on the client to restrict its activities to those which it is allowed to do, with no other enforcement by the server.

Some sites attempt to use firewalls to solve their network security problems. Unfortunately, firewalls assume that "the bad guys" are on the outside, which is often a very bad assumption. Most of the really damaging incidents of computer crime are carried out by insiders. Firewalls also have a significant disadvantage in that they restrict how your users can use the Internet. (After all, firewalls are simply a less extreme example of the dictum that there is nothing more secure then a computer which is not connected to the network --- and powered off!) In many places, these restrictions are simply unrealistic and unacceptable.

Kerberos was created by MIT as a solution to these network security problems. The Kerberos protocol uses strong cryptography so that a client can prove its identity to a server (and vice versa) across an insecure network connection. After a client and server has used Kerberos to prove their identity, they can also encrypt all of their communications to assure privacy and data integrity as they go about their business.

Kerberos is freely available from MIT, under copyright permissions very similar those used for the BSD operating system and the X Window System. MIT provides Kerberos in source form so that anyone who wishes to use it may look over the code for themselves and assure themselves that the code is trustworthy. In addition, for those who prefer to rely on a professionally supported product, Kerberos is available as a product from many different vendors.

In summary, Kerberos is a solution to your network security problems. It provides the tools of authentication and strong cryptography over the network to help you secure your information systems across your entire enterprise. We hope you find Kerberos as useful as it has been to us. At MIT, Kerberos has been invaluable to our Information/Technology architecture.

NTLM (NT LAN Manager)

NTLM is a suite of authentication and session security protocols used in various Microsoft network protocol implementations and supported by the NTLM Security Support Provider ("NTLMSSP"). Originally used for authentication and negotiation of secure DCE/RPC, NTLM is also used throughout Microsoft's systems as an integrated single sign-on mechanism. It is probably best recognized as part of the "Integrated Windows Authentication" stack for HTTP authentication; however, it is also used in Microsoft implementations of SMTP, POP3, IMAP (all part of Exchange), CIFS/SMB, Telnet, SIP, and possibly others.

The NTLM Security Support Provider provides authentication, integrity, and confidentiality services within the Window Security Support Provider Interface (SSPI) framework. SSPI specifies a core set of security functionality that is implemented by supporting providers; the NTLMSSP is such a provider. The SSPI specifies, and the NTLMSSP implements, the following core operations:

1. Authentication -- NTLM provides a challenge-response authentication mechanism, in which clients are able to prove their identities without sending a password to the server.

2. Signing -- The NTLMSSP provides a means of applying a digital "signature" to a message. This ensures that the signed message has not been modified (either accidentally or intentionally) and that that signing party has knowledge of a shared secret. NTLM implements a symmetric signature scheme (Message Authentication Code, or MAC); that is, a valid signature can only be generated and verified by parties that possess the common shared key.

3. Sealing -- The NTLMSSP implements a symmetric-key encryption mechanism, which provides message confidentiality. In the case of NTLM, sealing also implies signing (a signed message is not necessarily sealed, but all sealed messages are signed).

NTLM has been largely supplanted by Kerberos as the authentication protocol of choice for domain-based scenarios. However, Kerberos is a trusted-third-party scheme, and cannot be used in situations where no trusted third party exists; for example, member servers (servers that are not part of a domain), local accounts, and authentication to resources in an untrusted domain. In such scenarios, NTLM continues to be the primary authentication mechanism (and likely will be for a long time).

How to make sure that you are using Kerberos authentication

SQL Server 2005 supports Kerberos authentication indirectly through the Windows Security Support Provider Interface (SSPI) when you are using Windows integrated authentication instead of SQL authentication. However, SQL Server will only use Kerberos authentication under certain circumstances when SQL Server can use SSPI to negotiate the authentication protocol to use. If SQL Server cannot use Kerberos authentication, Windows will use NTLM authentication. For security reasons, we recommend that you use Kerberos authentication instead of NTLM authentication. Administrators and users should know how to make sure that they are using Kerberos authentication for remote connections.

To use Kerberos authentication, you must make sure that all the following conditions are true:

• Both the server and the client computers must be members of the same Windows domain or members of trusted domains.

• The server's service principal name (SPN) must be registered in the Active Directory service.

• The instance of SQL Server 2005 must enable the TCP/IP protocol.

• The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client's protocol order. Or you can add the prefix "tcp:" in the connection string to specify that the connection will use the TCP/IP protocol.

After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid 

If SQL Server is using Kerberos authentication, a character string that is listed as "KERBEROS" appears in the auth_scheme column in the result window.

The Basics of Hyper-Threading: What is it?

The Basics of Hyper-Threading: What is it?

Modern processors can only handle one instruction from one program at any given point in time. Each instruction that is sent to the processor is called a thread. What I mean is that even though it looks like you're multitasking with your computer (running more then one program at a time) you're really not.

The CPU will divide it's time and power evenly between all the programs by switching back and forth. This little charade of switching back and forth tricks the end user (you and me) and gives us the sense of multitasking.

Dual CPU based systems can work on two independent threads of information from the software but each processor is still limited at working on one thread at any given moment though. The software must be able to dish out two separate pieces of information like Win2000 or Adobe Photoshop for a dual processor system to be really used, by the way.

So what's new with the Pentium4 3.06GHz processor?

The Pentium 4 3.06 GHz processor is the first Intel desktop processor in history that can process two independent threads at the same time. With a SMT (Simultaneous Multi-Thread) enabled OS like Win2000/XP, Linux, etc. the operation system will identify the P4 3.06 GHz CPU as two logical processors that share the single physical CPU's resources. A physical processor can be thought of as the chip itself, whereas a logical processor is what the computer sees - with Hyper-Threading enabled the computer can have one physical processor installed in the motherboard, but the computer will see two logical processors, and treat the system as if there were actually two processors.

Even when we put a regular processor under 100% load, we're never fully utilizing 100% of the execution units. With a HyperThreading enabled processor those spare execution units can used towards computing other things now.

In the last HyperThreading enabled processor, both threads are simultaneously being computed, and the CPU's efficiency has increased from around 50% to over 90%!

The last example is of dual HyperThreading enabled processors which can work on four independent threads at the same time. Again CPU efficiency is around 90% (and in this case there would be four logical processors, and two physical processors).

While this all sounds very good in the above example, it's also about the most ideal situation ever - so let's get back to the real world.

If you were to run two pieces of software that are completely different, and use different execution units, your system performance should get a noticeable boost however!

The operating system will also play a key role in how well HyperThreading works. The OS assigns operations to the independent logical processors, and so if it's determined that one of the logical CPU's is to remain idle, the OS will issue a HALT command to the free logical processor thus devoting all of the other system resources to the working logical processor.

As you can see, HyperThreading has the potential to significantly boost system performance under certain circumstances. From Xeon/HyperThreading test results over at Anandtech you get a feel that HyperThreading Technology is still in it's infancy in terms of real added value across the board. As it stands now, the performance that HT brings to the table is still very application specific. Some programs will notice a performance boost, and in some cases, other programs will see a performance hit with HT enabled.

As more and more software is written specifically for HyperThreading, the performance differences will grow larger. It didn't take long for software developers to adopt SSE2, hopefully they'll jump on the HyperThreading bandwagon quickly and the consumer will see the benefit of this this ingenuous technology quickly

Networking Terms

Networking Terms

VIP

A virtual IP address (VIP or VIPA) is an IP address that is not connected to a specific computer or network interface card (NIC) on a computer. Incoming packets are sent to the VIP address, but all packets travel through real network interfaces.

VIPs are mostly used for connection redundancy; a VIP address may still be available if a computer or NIC fails because an alternative computer or NIC replies to connections

We can also use VIP for load balancing and Automatic Failover

Port Number

Definition: A port number represents an endpoint or "channel" for network communications. Port numbers allow different applications on the same computer to utilize network resources without interfering with each other.

Port numbers most commonly appear in network programming, particularly socket programming. Sometimes, though, port numbers are made visible to the casual user. For example, some Web sites a person visits on the Internet use a URL like the following:

In this example, the number 8080 refers to the port number used by the Web browser to connect to the Web server. Normally, a Web site uses port number 80 and this number need not be included with the URL (although it can be).

In IP networking, port numbers can theoretically range from 0 to 65535. Most popular network applications, though, use port numbers at the low end of the range (such as 80 for HTTP). The port number is included as a field within the header of each IP packet.

Note: The term port also refers to several other aspects of network technology. A port can refer to a physical connection point for peripheral devices such as serial, parallel, and USB ports. The term port also refers to certain Ethernet connection points, such as those on a hub, switch, or router.

You can find the list of port number at below URL

http://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers

UDP (User Datagram Protocol)

Definition: UDP is a lightweight transport built on top of IP. UDP squeezes extra performance from IP by not implementing some of the features a more heavyweight protocol like TCP offers. Specifically, UDP allows individual packets to be dropped (with no retries) and UDP packets to be received in a different order than they were sent.

UDP is often used in videoconferencing applications or games where optimal performance is preferred over guaranteed message delivery. UDP is one of the oldest network protocols, introduced in 1980 in RFC document 768.

DNS Server (Domain Name System)

Definition: The DNS translates Internet domain and host names to IP Addresses. DNS automatically converts the names we type in our Web browser address bar to the IP addresses of Web servers hosting those sites.

DNS implements a distributed database to store this name and address information for all public hosts on the Internet. DNS assumes IP addresses do not change (are statically assigned rather than dynamically assigned).

The DNS database resides on a hierarchy of special database servers. When clients like Web browsers issue requests involving Internet host names, a piece of software called the DNS resolver (usually built into the network operating system) first contacts a DNS server to determine the server's IP address. If the DNS server does not contain the needed mapping, it will in turn forward the request to a different DNS server at the next higher level in the hierarchy. After potentially several forwarding and delegation messages are sent within the DNS hierarchy, the IP address for the given host eventually arrives at the resolver, that in turn completes the request over Internet Protocol.

DNS additionally includes support for caching requests and for redundancy. Most network operating systems support configuration of primary, secondary, and tertiary DNS servers, each of which can service initial requests from clients. ISPs maintain their own DNS servers and use DHCP to automatically configure clients, relieving most home users of the burden of DNS configuration.

Also Known As: Domain Name System, Domain Name Service, Domain Name Server

Dynamic Host Configuration Protocol Definition: DHCP allows a computer to join an IP-based network without having a pre-configured IP address. DHCP is a protocol that assigns unique IP addresses to devices, then releases and renews these addresses as devices leave and re-join the network.

Internet Service Providers (ISPs) usually use DHCP to allow customers to join the Internet with minimum effort. Likewise, home network equipment like broadband routers offers DHCP support for added convenience in joining home computers to the LAN.

DHCP environments require a DHCP server set up with the appropriate configuration parameters for the given network. Key DHCP parameters include the range or "pool" of available IP addresses, the correct subnet masks, plus gateway and name server addresses.

Devices running DHCP client software can then automatically retrieve these settings from DHCP servers as needed. Using DHCP on a network means system administrators do not need to configure these parameters individually for each client device.

NetBIOS Definition: NetBIOS is a software protocol for providing computer communication services on local networks. Microsoft Windows uses NetBIOS on Ethernet or Token Ring networks.

Software applications on a NetBIOS network locate each other via their NetBIOS names. A NetBIOS name is up to 16 characters long and in Windows, separate from the computer name. Applications on other computers access NetBIOS names over UDP port 137. It provides name resolution services for NetBIOS.

Two applications start a NetBIOS session when one (the client) sends a command to "Call" another (the server) over TCP port 139 on a remote computer. Both sides issue "Send" and "Receive" commands to deliver messages in both directions. The "Hang-Up" command terminates a NetBIOS session.

NetBIOS also supports connectionless communications via UDP datagrams. Applications listen on UDP port 138 to receive NetBIOS datagrams.

NetBIOS and NetBEUI are separate but related technologies. NetBEUI extends NetBIOS with additional networking capabilities.

Also Known As: Network Basic Input/Output System

WINS Definition: The Windows Internet Naming Service (WINS) supports name resolution, the automated conversion of computer names to network addresses, for Windows networks. Specifically, WINS converts NETBIOS names to IP addresses on a LAN or WAN.

Like DNS, the Windows Internet Naming Service employs a distributed client/server system to maintain the mapping of computer names to addresses. Windows clients can be configured to use primary and secondary WINS servers that dynamically update name/address pairings as computers join and leave the network. The dynamic behavior of WINS means that it also supports networks using