Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

1. Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.

          Use surface area configuration tool, to allow remove connections.

 

1. Turn on the SQL Server Browser service.

(Start -> Run -> Services.msc -> start the SQL Server browser services)

 

1. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

 

For SQL Server Service (MSSQLServer)

Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties - Advanced Tab -> Windows Firewall Settings - Add Port -> In the Name text box type SQL Server Instance Name -> in the PORT number text box type 1433 -> Select TCP/IP -> OK

 

For SQL Browser Service

Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties - Advanced Tab -> Windows Firewall Settings - Add Port -> In the Name text box type SQL Server Browser Service -> in the PORT number text box type 1434 -> Select UDP -> OK

 

Wishing you all Very happy and prosperous new year

[caption id="attachment_185" align="alignnone" width="497" caption="Happy New Year"][/caption]

How do I install the Service Pack?

[polldaddy poll=1234626] 

Follow the below steps before installing service packs on production server.

Step 1: Get all the information about the latest service packs and their reviews

 

Step 2: Install Service Pack on test server

 

Step 3: Test the application functionality with the SQL Server with new service pack

 

Step 4: If application works properly with the Test database server, take the sign off from business team and development team.

 

Step 5: Before applying service pack on production server, please do the below steps

Cold Backup

Take a full server backup from the Operating System level with the SQL Server services turned off.

Make a note of startup parameter, SQL Server Services Users/Network rights, memory allocation etc.

This will guarantee you can return to a previous state if something goes wrong during the upgrade

 

Hot Backup

Turn on the SQL Server Services and take the backup of all system databases (Master, Model, MSDB and Copy the Resource database file) and user databases

 

Step 6: Plan the downtime of the production server for the Service Pack installation and inform the users for the same.

 

Step 7: Create the backup and restore plan for “What to do if the application is not working properly after service pack installation?”

 

Step 8: Download the latest service pack and install it using “Setup.exe”

 

Step 9: Again test the production application and production database server are working properly or not.

 

 

SQL Server Service Packs and Hotfixes

What is Service Pack and Hot fixes in SQL Server?

With respect to SQL Server, if SQL Server has some in house error/bugs. We can’t resolve it. To remove product level error Microsoft has implemented two ways.

1. Service Packs


2. Hot fixes

 

What is the difference between a Service Pack and a Hotfix?

Service Packs normally have three properties:

·    Provide security fixes

• Correct software errors


• Enhance performance

Service Packs don’t add new functionality or change the interface dramatically. Service Packs are bundled into a programmed delivery method, and are cumulative. That means that you can install Service Pack three without applying Service Pack two, or even one. They are for general use — pretty much everyone should install the Service Pack

 A Hotfix is usually a specific security or software flaw that is addressed in code. There may or may not be a packaged delivery method — some Hotfixes just come with instructions of how and where to copy the patch. Hotfixes are normally not for everyone — Microsoft states that you should only apply the patch if you're having the specific problem it addresses. Even then, some Hotfixes are only available from a Microsoft support representative.

 

How to check which Service Pack installed?

Run below SQL Server command

SELECT SERVERPROPERTY('productversion')

, SERVERPROPERTY ('productlevel')

, SERVERPROPERTY ('edition')

GO

If you’re using SQL Server version 7, you need a different command:

SELECT @@VERSION

GO

 

The first line of what you will see shows the version number of SQL Server software installed on your server. The last three digits (called the build number) of the version number are used to determine the Service Pack installed on your SQL Server.

 

How to find which Hotfix applied on SQL Server?

It's a bit more difficult to determine the Hotfix number, since each one will be different, and many may be installed. To determine the Hotfixes on your server, download the program called HFNetChk (http://support.microsoft.com/kb/305385) from Microsoft. There's a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

There are now stored procedures you can use to show you the build number on your server, such as:

EXEC sp_server_info 

GO

and 

EXEC master..xp_msver

GO

 

How to download/get the latest service packs?

You can download the latest service packs from the below URL

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx

 

 

 

How do we see the SQL Plan in textual format?

 

SET SHOWPLAN_TEXT ON

To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.

SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.

Example

USE Northwind;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI';
GO
SET SHOWPLAN_TEXT OFF;
GO

Displays how indexes are used:
--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Northwind].[dbo].[Customers].[CustomerID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)

Some Important Points :

1. SET SHOWPLAN_TEXT cannot be specified when using a stored procedure

2. You need to have the SHOWPLAN permission while running SET SHOWPLAN_TEXT

 

How to enable or disable 'sa' login?

/* To disable 'sa' Login */
ALTER LOGIN [sa] DISABLE
GO
/* To Enable 'sa' Login */
ALTER LOGIN [sa] ENABLE
GO

SQL Server 2005 Questions and Answers

1.    What does integration of .NET Framework mean for SQL Server 2005?

This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in .net compatible language.

2.    What is SSIS?

According to Microsoft SQL Server Integration Services, “(SSIS) is an effective set of tools for both the traditional demands of ETL operations, as well as for the evolving needs of general purpose data integration.” In short, it is the next version of DTS (Data Transformation Services). ETL stands for Extract, Transform and Loading. In short it is a data migration tool that is flexible, fast, and has scalable architecture that enables effective data integration in current business environments.

3.    What is MARS?

In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Multiple Active Result Sets (MARS) is the ability to have more than one pending request under a given SQL Server connection. MARS is a programming model enhancement that allows multiple requests to interleave in the server. We need to note that it is not a parallel execution in the server. However, it may benefit us with some performance benefits if used correctly. By default, this feature is not set in SQL Server 2005.

4.    What are the Security Enhancements in SQL Server 2005?

SQL Server 2005 enables administrators to manage permissions at a granular level.

-> In the new SQL Server 2005, we can specify a context under which statements in a module can execute.

-> SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.

-> Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain.

-> SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client-server communications are encrypted.

5.    What is new with the Reporting services in SQL server 2005?

SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.

-> Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity

-> Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio

-> Report Builder – A new reporting tool that enables business users to create their own reports

6.    What is OLAP?

Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.

7.    What is Data Mining?

According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant ways. Data Mining is a platform for developing intelligent applications, not a stand-alone application. You can build custom applications that are intelligent because the data mining models are easily accessible to the outside world. Further, the model is extensible so that third parties can add custom algorithms to support particular mining needs.

8.    What is new with the Analysis Services (SSAS) in SQL Server 2005?

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.

·         Supports up to 16 instances of Analysis Services Service.

·         As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.

·         Uses the Proactive caching.

9.    What is Information Schema in SQL Sever 2005?

Information Schema is the part of the SQL- 92 standard which exposes the metadata of the database. In SQL server, a set of views are created in each of the databases which exposes the metadata of the database. The information schema is kept in a separate schema - information schema - which exists in all databases, but which is not included in the search path by default. For more information regarding Information schema please read this article.

10. What is Full Text Search? How does it get implemented in SQL server 2005?

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

11. What is integration of Microsoft Office System mean?

The integration with Microsoft Office system means the following.

· Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind the scenes to perform powerful end user analysis on spreadsheet data.

· Data Mining Client for Excel: Offers a full data mining model development lifecycle directly within Excel 2007.

· Data Mining Templates for Visio: Enables powerful rendering and sharing of mining models as annotatable Visio 2007 drawings.

12. What is the support of Web Services in SQL Server 2005?

With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. With this new feature we can now connect to SQL Server not only with TDS- Tabular data stream (a binary protocol for connecting to SQL Server 2005) but also over SOAP/ HTTP.

13. What is OLTP?

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

14. What is Snapshot in SQL Server 2005?

A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation.

15. What is snapshot isolation in SQL Server 2005?

SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.

16. What is Database Partitioning in SQL Server 2005?

SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.

17. What is SQL Server Agent?

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. We generally schedule the backups on the production databases using the SQL server agent. In SQL Server 2005 we have roles created for using SQL Server agents.

·         SQLAgentUserRole

·         SQLAgentReaderRole

·         SQLAgentOperatorRole

SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service.

18. What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?

“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.

· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.

· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.

19. What are Business Logic Handlers?

Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic handler can perform one of the following three actions.

· Reject Data

· Accept Data

· Apply Custom Data

20. What are different variants of SQL Server 2005?

There are different variants of SQL Server 2005 commercially available.

· Express – Free and only for one user

· Enterprise – 5 users apart from server

· Workgroup – 10 users apart from server

· Standard – 25 users apart from server

21. What are Various Service packs available for SQL Server 2005?

As of now there are two service packs available for the SQL Server 2005.

· Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data Programmability, SSIS, and reporting services.

· Service Pack 2 – Unlike Service Pack 2, this service pack enables SQL Server 2005 customers to take advantage of the enhancements within Windows Vista and the 2007 Office system.

22. What are the New Data types introduced in SQL Server 2005?

SQL Server 2005 has added some new data types to its existing data types.

. XML Data type

· VARCHAR (MAX)

· NVARCHAR (MAX)

· VARBINARY (MAX)

As we can see, the new term MAX has been introduced in SQL Server 2005. This new specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.

23. Does SQL Server 2005 support SMTP?

SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an e-mail was not supported in the previous versions of SQL Server.

24. What is SQL Management Object is SQL Server 2005?

These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a database, calling methods of the database that returns a table, using transactions, transferring data, scheduling administrative tasks, etc. The best part about SMO is that most of it can also be used with SQL server 2000.

25. What is SQL Service Broker in SQL Server 2005?

SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages are called conversations.

 

DBA Roles and Responsibilities

Click Me to Check How to be DBA?

DBA Responsibilities

• Installation, configuration and upgrading of Microsoft SQL Server/MySQL/Oracle server software and related products.


• Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.


• Establish and maintain sound backup and recovery policies and procedures.


• Take care of the Database design and implementation.


• Implement and maintain database security (create and maintain users and roles, assign privileges).


• Database tuning and performance monitoring.


• Application tuning and performance monitoring.


• Setup and maintain documentation and standards.


• Plan growth and changes (capacity planning).


• Work as part of a team and provide 7x24 supports when required.


• Do general technical trouble shooting and give consultation to development teams.


• Interface with MSSQL/MySQL/Oracle for technical support.


• ITIL Skill set requirement (Problem Management/Incident Management/Chain Management etc)

Types of DBA

1. Administrative DBA - Work on maintaining the server and keeping it running. Concerned with backups, security, patches, replication, etc. Things that concern the actual server software.


2. Development DBA - works on building queries, stored procedures, etc. that meet business needs. This is the equivalent of the programmer. You primarily write T-SQL.


3. Architect - Design schemas. Build tables, FKs, PKs, etc. Work to build a structure that meets the business needs in general. The design is then used by developers and development DBAs to implement the actual application.


4. Data Warehouse DBA - Newer role, but responsible for merging data from multiple sources into a data warehouse. May have to design warehouse, but cleans, standardizes, and scrubs data before loading. In SQL Server, this DBA would use DTS heavily.


5. OLAP DBA - Builds multi-dimensional cubes for decision support or OLAP systems. The primary language in SQL Server is MDX, not SQL here

Application DBA- Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.

Download SQL Server Interview Question

Datbase Snapshot

What is database snapshot?
A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created. 

 

Is database snapshot transitionally consistent?
Yes.
Each database snapshot is transitionally consistent with the source database at the moment of the snapshot's creation. When we create a database snapshot, the source database will typically have number open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transitionally consistent. Just like it follows the recovery interval step and it will not affect the source database. 

 

What are the uses of database snapshot?
Reporting Purpose
we can recover damaged database using database snapshot
also useful if we are planning to do major change in the source database
Mirroring database is always in recovering mode, to read that database we can use database snapshot.
Which edition of SQL Server 2005 supports database snapshot? Enterprise Edition 

 

Is Developer edition of SQL Server 2005 supports database snapshot?
No. 

 

What is copy-on-write operation in database snapshot?
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.

 

What is Sparse file in Database Snapshot?

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.

 

What is NTFS File System?

The NTFS acronym stands for New Technology File System. The name derives from the implementation of very innovative data storage techniques that were refined in NTFS. While none of the techniques are unique to NTFS, it is the first time that so many innovations were released at once on a production file system. The FAT file system had long been criticized for not including some of the more obvious improvements such as journaling, disk quotas, and file compression. However, these improvements made NTFS incompatible with previous versions of Windows, and also with hard disk tools designed for FAT file systems. For example, data recovery tools such as GetDataBack and partitioning tools such as PartitionMagic would run on Windows NT, yet could not function on the newer file system. This led to much frustration with users who had purchased licenses for these products before upgrading to Windows NT.

 

Explain Sparse file Size in database snapshot

At the creation time sparse file will take very little space, but as the data changes occurred into the parent database, data page before the changes copied into the sparse file. Thus sparse file grows. Sparse files are the feature of NTFS file system. As the sparse file grows NTFS will allocate the space to sparse file gradually.

 

Why does the size of a sparse file slightly exceed than the space actually filled by pages in it?

Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB. The latest 64-KB increment holds from one to eight 8-KB pages, depending on how many pages have been copied from the source database. This means that, on the average, the size of a sparse file slightly exceeds the space actually filled by pages.

 

How to create database snapshot?

1. Based on the current size of the source database, ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.


2. Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database.

 

Syntax

CREATE DATABASE AdventureWorks_SS ON

(

NAME = AdventureWorks_Data,

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss'

)

AS SNAPSHOT OF AdventureWorks

 

 

 

 

Factors That Impact Performance

 

Factors That Impact Performance

Application Architecture

Application Design

Transactions and Isolation Levels

Transact-SQL Code

Hardware Resources

SQL Server Configuration

What does database performance means?

What does database performance means?

Database performance enhancement refers to below terms.

Response time: Refers to the interval between the time when a request is submitted and when the first character of the response is received.

Throughput: Refers to the number of transactions that can be processed in a fixed unit of time.

Scalability: Refers to how the throughput and/or the response time changes as we add more hardware resources. In simple terms, scalability means that if you are hitting a hardware bottleneck, you can alleviate it simply by adding more resources.

 

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