Yearly Archives: 2008

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

 

Advertisements

How do I install the Service Pack?

 

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

 

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.