My Favorite Blogs

Friday, December 24, 2010

A network-related or instance-specific error occurred while establishing a connection to SQL Server

Last week I was installing SQL Server 2008 R2 on a 64 bit Windows 2008 box, after the installation I suppose to do a post installation check that includes connectivity test both local and remotely. I ensured that the SQL services are running under expected network domain account. When I tried to connect the SQL instance remotely the SQL kept throwing connection error and I couldn’t get into the instance and the below error popped up.


Error Thrown

TITLE: Connect to Server
------------------------------
Cannot connect to SQL2008INS01.
------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

-----------------------------
BUTTONS:

OK
------------------------------

After reading some knowledge bases and forums I understood the follows. In Windows Server 2008 and Vista the default dynamic port range for outgoing connections has been increased, the default start port is 49152 and end port is 65535 where as the earlier versions has between 1025 through 5000. By default the SQL Server establish it connections through TCP port 1433 and the windows firewall will accept connections.

In windows server 2008 the firewall is on and blocking all remote connections by default. This ensures that Windows 2008 does not leave the TCP port open by default not the SQL 2008 installation. We have to create a firewall rule to allow the server to send and receive traffic from SQL Server.

So make sure to add this step in our SQL 2008 post installation step on Windows Server 2008 to open the TCP port 1433.

By default, the Windows Firewall blocks all incoming unsolicited network traffics, we need to configure firewall rules for program, port or system/app services to connect a server remotely.

Following are steps to configure firewall rules for TCP port 1433 to accept SQL Server connection request remotely.

Step 1: Go to Start Administrative Tools  click on Windows Firewall and Advanced Security, on Windows Firewall and Advanced Security page, right click on Inbound Rules and click on New Rule(Refer Fig1).



Fig 1
 
Step 2: The New Rule will navigate to Rule Type window, here you can chose either program, port , predefined or a custom rules. Make sure you have chosen Port and click Next(Refer Fig2).


Fig2

Step 3: Now you are on the Protocol and Ports settings window, make sure the rules applies to TCP and type 1433 on Specific local ports and click Next(Refer Fig 3).


Fig 3
 Step 4: After specifying the port number 1433, you have to specify the action to be taken by Windows Firewall when it receives a connection that matches with the specified conditions, so choose “Allow the Connections” and click Next to move further (Refer Fig 4).


Fig 4
Step 5: Now choose the profile to apply the rule. Since most of the business allows their employees to work remotely and these three profiles will be used most common. Choose all three and move next (Refer Fig 5)




Fig 5


Step 6: Finally specify the name of the Firewall rule and Finish it(Refer Fig6).


Fig 6


Reference

1) The default dynamic port range for TCP/IP has changed in Windows Vista and in Windows Server 2008

2) Windows Server 2008 Default firewall Settings

3) How to: Configure a Windows Firewall for Database Engine Access

4) How do I open the firewall port for SQL Server on Windows Server 2008 programmatically?

Tuesday, July 27, 2010

Microsoft SQL Server 2008 R2 Best Practices Analyzer how to audit or analyze SQL Server Configurations.

What is Best Practices Analyzer (BPA)?
Microsoft Best Practices Analyzer is a diagnostic tool which uses to collect information about a physical Server and SQL Server instances. The tool will verify the settings and configurations that are according to the recommended best practices by Microsoft. This tool will lists all configurations and settings differ from the best practice recommendations and list potential problems in the installed SQL server instance and recommend the resolutions for the problems.

Microsoft SQL Server 2008 R2 BPA is pre-requisite installed Microsoft Baseline Configuration Manager (MBCA V2) framework and PowerShell V2.0 on either on Windows Server 2003/2008/2008 R2 or Windows Vista or Windows Vista operating systems, MBCA is a new framework comes with Windows Server 2008 R2 and Windows 7 versions and it is a new standard and utilizes a model architecture made up of xml and PowerShell scripts. It supports both local and remote execution of BPA against both local and remote servers. We can also use both GUI and command line scanning abilities.

This version of BPA supports the SQL Server Database Engine, SQL Setup, Replication, SSAS, SSRS and SSIS as well. This tool will give a proactive advice to the SQL Users about the settings of an installed SQL instance.

How to Install?
Make sure the Windows PowerShell installed on the server, the SQL 2008 BPA R2 is built on the MBCA V2 framework, so install the MBCA V2 first then install the SQL 2008 R2 V2 on the server. It has simple steps to install the tool and nothing specific to specify while install.

How to Analyze?

Microsoft Baseline Configuration Analyzer 2.0 (MBCA) is the interface for SQL 2008 R2 Best Practices Analyzer (BPA). Go to Start, All Programs , click on MBCA 2.0, you will get the GUI wizard to execute the SQL Server 2008 R2 BPA.

In the Select a Product Combo box select “SQL Server 2008 R2 BPA” (that would be the only one listed in) by default it will be connected to the local SQL instance to analyze and if needed you can connect to a remote computer by clicking “Connect to Another Computer” link on the right side of the combo box. Click on the “Start Scan” link just below the combo box (See Figure 1) to scan the SQL configurations as per Microsoft’s Best Practice Analyze standards.
 
Figure 1

When you click on the “Start Scan” the next screen will be popup to enter parameters to do the analysis. Enter the Physical Server name, SQL instance name (if differed from server name else same) in the text boxes and check the option boxes to scan the configurations to analyze (See Figure 2).
Figure 2

Click on the “Start Scan” hyper link and scanning will be started and in progress. (See Figure 3)

Figure 3

 Once the scanning process completed then you can see the Baseline Configuration Analyzer Report, on the report page you can find two tabs Non-complaint and all (see Figure 4), the Non-complaint tab will list all the errors and warnings. The below errors says that the backup of the databases in the instance are outdated mean that there is no recent backup for the databases. The Replication “Replication is not configured” error shown up because there is no replication configured in the instance but I selected the option to scan the replication settings.

Figure 4

On the All tab you can find the analyze results on each categories (see Figure 5)

Figure 5

I intentionally copied a backup file on the data drive before the scan process and it caught this as an issue and specified as “Database files and backup files exists on the same volume” and also given Impact and Resolution for the issue. (See Figure 6)

Figure 6 

Conclusion
The BPA tool analyze the recommended best practices by Microsoft to bring the SQL Server up to the standard to avoid any configuration or settings issues in an environment. The noted feature for SQL Server 2008 R2 BPA is able to scan both the local computer and remote computers.

Download
BPA: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
MBCA: http://www.microsoft.com/downloads/details.aspx?familyid=1B6E9026-F505-403E-84C3-A5DEA704EC67&displaylang=en

Saturday, July 17, 2010

SQL Server 2005 - DBMail

Steps to Activate DBMail in SQL Server 2005/2008

1. Enable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

2. Create DBMail Account

USE [master]
GO
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA' ,
@email_address = 'DBMail@
sqldbops.com' ,
@display_name = 'DBMail' ,
@description = 'Mail account for administrative e-mail' ,
@mailserver_name = 'mail.eweb.sqldbops.com' ,
@port = 25
, @use_default_credentials = 1


3. Create DBMail Profile

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DBAProf',
@description=N'Profile used for administrative mail'

4. Add Account to the profile created in step-3

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DBAProf', @account_name=N'DBA', @sequence_number=1

5. Verify if profile created successsfully

SELECT * FROM [msdb].[dbo].[sysmail_profile] as p JOIN [msdb].[dbo].[sysmail_profileaccount] as pa
ON p.profile_id = pa.profile_id

6. Add profile to Public. If you declare as Public, you don't have specify the profile when sending mails using sp_send_dbmail proc. If declared Private, then profile should be specified

EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'DBAProf', @is_default=1

6. Update Registry for SQLServer Agent:(Useful for Cluster env)

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBAProf'

You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally enables DBMail for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

7. Verify if Value shows 1 to ensure AgentXps are enabled

USE [master]
GO
select value_in_use from sys.configurations where configuration_id = 16384


8. Create an Operator

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'surpal@sqldbops.com',
@category_name=N'[Uncategorized]'

9. Add Operator to all jobs using the script..

DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)


SELECT JobName = '%'
DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name LIKE @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N''DBA'''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor

10. Restart SQL Server Agent

Steps to De-Activate DBMail in SQL Server 2005/2008

1. Remove Operator from all jobs using the script


DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)

DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name = @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'''''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor


2. Drop DBMail Profile

EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name=N'DBAProf', @force_delete=True


3. Drop DBMail account

EXEC msdb.dbo.sysmail_delete_account_sp @account_name=N'DBA'


4. Remove the entry from DBMail Registry for SQLServer Agent

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
GO


You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally removes DBMailprofile for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

5. Remove Operator from SQL Server Agent

EXEC msdb.dbo.sp_delete_operator @name=N'DBA'

6. Disable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'0'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO






Tuesday, June 29, 2010

TDE and Tempdb

If you use TDE in any of your SQL instances, then the Tempdb also automatically gets encrypted on that instance. This could have a performance effect for the other unencrypted databases. Is this true or false?

Answer this question here

Tuesday, May 25, 2010

Token-based server access validation failed with an infrastructure error - MS SQL Server 2008 on Windows 2008 Server

Issue
Recently I faced login failure issue while logging into a newly built MS SQL Server 2008 instance on Windows Server 2008 R2 server. I’ve ensured that I’m part of the local administrators group in the server even though I’m getting the login failure and SQL didn’t allow me to connect to the instance through SSMS (SQL Server Management Studio).

SQL ErrorLog
2010-05-24 11:27:55.480 Logon Login failed for user 'SQLDBOps\Krishjay'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

Event Log
+ System
- Provider
[ Name] MSSQLSERVER
- EventID 18456
[ Qualifiers] 49152
Level 0
Task 4
Keywords 0x90000000000000
- TimeCreated
[ SystemTime] 2010-05-24T18:15:34.000000000Z
EventRecordID 2166
Channel Application
Computer SQLDBOPs
- Security
[ UserID] S-1-5-21-606947145-1563985344-839528115-120123
- EventData
SQLDBOps\Krishjay
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
[CLIENT: ]

Reason for the issue:
After going through couple of forums and knowledge base articles then I understood that Windows 2008 new Security feature UAC (User Access Control) is blocking me to login to the SQL Instance.

What is User Access Control?
UAC is a new Security feature introduced in Windows Server 2008 (also applies to Windows Server 2008 R2, Windows 7 and Windows Vista). When an administrator logs on to a computer running Windows 2008, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed, resulting in a standard user access token. The standard user access token is then used to start the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well.

So the UAC is not passing all my group membership credentials when I log on to the server that’s why I do not have privilege to connect the SQL instance on SSMS. Here if I add my login account explicitly in SQL level then I can connect to SQL without any issues. I mean the Standard user credentials (as per UAC) are not sufficient to connect the SQL instance on Windows 2008 server or you can say that the local admin group membership credentials are not inherited with my account.

Workaround:

To resolve this issue, there are two options, one is open the SSMS with option “Run as Administrator” and the other is “Disable UAC” in the box. Again the answer is depends on the environment and management decision how it needs to be.

Note: Run as Administrator is user level settings (and can be configured for all the users in the box) and Disable UAC is for all users and disabling the server protection from the malwares and viruses.

Run As Administrator Settings

Start --> All Programs-->Microsoft SQL Server-->SQL Server Management Studio -->Right Click --> Properties

The SSMS properties window will pop up and change the settings on the compatibility tab according to your environment.
 

1. Check “Run this program as an administrator” on Compatibility page if you are the only user using SSMS on this box.

2. If this setting required for other admin users on the box then Click on the “Change Settings for all users” button

3. And Check “Run this program as an administrator” for all users.

This will allow opening the SQL Server management studio for all the administrators on the box without any login issues.

Disable UAC:

Go to Command prompt and run the below command to disable the UAC

Reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

Other way of doing the same is Go to Registry Editor

Start --> Run -->Type RegEdit and hit Enter key


In the Registry Editor, Navigate to the below folder

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System

Right click on System folder and place the mouse pointer on New --> click on "DWORD Value" (New Value will be created under System) -->Rename it as "EnableLUA" and default value will be 0 leave as it is.

Then restart your Computer
Now you will start to get nagging prompts to re-enable UAC, if needed you can disable these prompts.
Click the bubble
At the side, it should say “Change the way Security Center alerts me”, Click that.
Click “Don’t notify me and don’t display the icon”

Now you have disabled UAC but keep in mind that you have disabled some of your protection against spyware and viruses.

Reference:

http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx
http://technet.microsoft.com/en-us/library/cc731416(WS.10).aspx
http://windows.microsoft.com/en-ZA/windows7/what-is-user-account-control
http://windows.microsoft.com/en-ZA/windows7/turn-user-account-control-on-or-off

Monday, May 24, 2010

MS SQL Server 2008 Administration Exam - Free from BrainBench

BrainBench announced the release of their new MS SQL Server 2008 Administration test. This certification test is available for free on their site for a limited time as they gather data to monitor its performance.

 
Be among the first to take this test and measure your level of expertise with the new content. These tests are developed as pre-employment assessments where MS SQL Server 2008 Administration is used significantly in the candidate’s job role. Once again, when helping with this test validation, completion of the test offers free certification

To take the test, go to http://www.brainbench.com/xml/bb/common/testcenter/taketest.xml?testId=2831 You may have to register if it is your first time taking IT tests with Brainbench.

Take some time and explore our entire library of free tests. Forward this message and challenge your colleagues to top your score!


Good luck!

Monday, May 3, 2010

How to create Policies using PowerShell scripts

Introduction

As you know that SQL Server 2008 supports Windows PowerShell and is a powerful scripting shell that helps administrators to automate and built robust server administration scripts. In my previous articles I’ve written about Using SQL Server 2008 PBM, how to monitor Free Space of data file(s) and How to Evaluate Policies on Multiple Instances using PowerShell scripts. In this article I’m going to walk you through how to create policies using PowerShell script.
In SQL 2008, policies can be created in two ways. One is using SSMS (SQL Server Management Studio) wizard and second is using PowerShell script. In my scenario I’ve been requested to create this script to deploy/create policies as a POST installation step. Suppose if you need to use this script for unattended SQL installations then make sure that this script called only after a complete SQL installation done on a server, because you need a SQL policy store to create your policies.Read More

Tuesday, March 30, 2010

Microsoft SQL Server 2008 R2 top 10 features


 Hey folks be prepare to read more about new enhancements in SQL Server 2008 R2 edition, a lot of attention has been given to the new database engine and BI features to landing on  the ground with SQL Server 2008 R2 edition. I’ve listed the top 10 features/enhancement here for your reference… Sure you will feel it is amazing…
1.       Multi-server management
2.       Data-tier applications (DAC)
3.       Master Data Services
4.       StreamInsight
5.       SSMS enhancements for SQL Azure
6.       Parallel Data Warehouse
7.       Enhanced scale-up capabilities
8.       PowerPivot for Excel and SharePoint
9.       Reporting Services improvements
10.   Report Builder 3.0

1.       Multi-server management

Multi-server management utility is a new feature in MS SQL 2008 R2 edition; it is very useful for DBAs to monitor their SQL instances from one instance. This feature named as UCP (Utility Control Point) you can setup this Multi-Server Management feature using View Menu à Utility Explorer. This feature is available only in SQL 2008 R2 and this will not support earlier versions.  Read more

2.       Data-tier applications (DAC)
DAC is a new feature in SQL Server 2008 R2, it is focused for database deployment best practice, DAC is a self-contained unit of deployment that enables data-tier developers and DBAs to package the SQL Server objects, including database and instance objects, into a single entity called DAC package. Developers can build a DAC package using the Data-tier Application project system in Visual Studio 2010, whereas SQL Server Management Studio (SSMS) users can extract a DAC (reverse engineering) and generate a DAC package file for an existing database. In SSMS you can find Extract Data-tier Application  (right click on database àTasksà Extract Data-tier Application) open which can be used to do reverse engineer your database (typically generate the script for all objects in a database) and can be deployed on different instance (Eg. Dev environment to Prod) using Deploy Data-tier Application (right click on Instance name àDeploy Data-tier Application option) Read More
3.       Master Data Services
MDS (Master Data Services) is a feature in BI platform which helps an Enterprise to manage centralized master data entities and hierarchies. With MDS, people across the enterprise can contribute to and consume master data securely and as needed, without having to go through IT and MDS provides web-based access to the master data hub for browsing, editing, authoring, auditing, and approving master data. Read More
4.       StreamInsight

MS SQL Server 2008 R2 StreamInsight feature is focused for event-driven applications where you need low latency requirements. What StreamInsight does is allow organizations to make decisions on streaming data in near real-time. An event-driven application use CEP (Complex Event Processing) technology and is different from traditional databases, and the new component could make SQL Server a better fit for areas like financial services and web analytics. Developers can develop the CEP applications using C# and .NET environments. StreamInsight implements a lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data. Read More

5.       SSMS enhancements for SQL Azure

The November CTP version of SQL Server Management Studio from SQL Server 2008 R2 and SQL Server 2008 R2 Express can be used to access, configure, manage and administer SQL Azure. Previous versions of SQL Server Management Studio are not supported. Read More

6.       Parallel Data Warehouse

MS SQL Server 2008 R2 PDW (Parallel Data Warehouse) (previously code named Project Madison) allows data warehouses to be scaled over several physical servers that deliver really high performance through a MPP (Massive Parallel Processing) architecture at a low cost on Windows 2008 R2 with industry-standard hardware. “In symmetric multi-processing (SMP) architecture, query processing occurs entirely within one physical instance of a database. CPU, memory and storage impose physical limits upon speed and scale.” A SQL Server 2008 R2 Parallel Data Warehouse MPP data warehouse appliance partitions large tables across multiple physical nodes, each node having dedicated CPU, memory and storage, and each running its own instance of SQL Server, in parallel shared nothing architecture “(oh man… my head is spinning …!)Read More

7.       Enhanced scale-up capabilities

MS SQL Server 2008 R2 with Windows 2008 R2 now supports up to 256 logical processors with the new data center editions up from 64. It is a lot more in SQL and really a big step forward by Microsoft with SQL Server.  Also it supports Memory in excess of 2 terabytes. Read More

8.       PowerPivot for Excel and SharePoint

Finally the Project Gemini has been integrated with MS SQL Server 2008 R2, it officially named as PowerPivot and is integrated with SharePoint 2010. “Use PowerPivot to expand the reach of BI in your organization through familiar technologies and platforms from Microsoft.” Read More

9.       Reporting Services improvements

In MS SQL Server 2008 R2 BI brings lots of new enhancements in the existing tools which you can feel more in SQL Server Reporting Services (SSRS). SQL Server 2008 R2 version of Business Intelligence Development Studio helps lot easier to open, modify, preview, save, and deploy both earlier and later versions of reports and Report Server projects by using the later version of Business Intelligence Development Studio. Read More

10.   Report Builder 3.0

The new version of Report Builder 3.0 brings plenty of data visualization capabilities including maps, sparklines and databars (this feature not available in SQL 2008). This also brings with it new capabilities for working with gauges and charts in SQL Server Reporting Services (SSRS), and while it is part of the R2 suite of tools, the tool can be downloaded separately to run with previous versions of SQL Server as well. Read More 1 Read More 2
You may feel like I covered here very little; of course yes it is, because we can write numerous pages here to talk about the new features and enhancements in MS SQL Server 2008 R2 edition, but I’m leaving that part with you…. Enjoy…

Sunday, March 28, 2010

How to Evaluate Policies on Multiple Instances


Introduction
In my previous article I’ve written about using SQL Server 2008 Policy -Based Management (PBM) and how to use it to monitor Free Space of data file(s) . When you evaluate the policy, it will evaluate each data file(s) on each database on the instance and the results can be viewed using the syspolicy_policy_execution_history and syspolicy_policy_execution_history_details system views in msdb. In this article I’m going to walk you through how to evaluate policies on multiple instances (including down-level instances SQL 2000 and 2005) using the EPM (Enterprise Policy Management Framework) through PowerShell. Read complete article

Thursday, March 4, 2010

Getting set up for multi-server management with SQL Server 2008 R2


One of SQL Server 2008 R2's most anticipated features is centered around its new multi-server management capabilities. As of the current Community Technical Preview (CTP), this feature is referenced as the utility control point (UCP), so that is how it will be referred to in this article.

The UCP allows you to gather a large number of metrics about multiple SQL Server instances and display this data in an easy-to-use dashboard report. This report provides a quick overview of your entire enterprise from a single screen within SQL Server Management Studio. The utility control point does this by creating a database on the server that will function as your UCP. The monitored instances then upload their data to the UCP. Read more

Wednesday, January 27, 2010

How to Set the default database and log directories on MS SQL Instance using T-SQL commands

--Run the following script on Query Analyzer


USE master

--For Data files
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData',
    REG_SZ,
    N'H:\sql\data'


--For Log  files
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog',
    REG_SZ,
    N'O:\sql\Log'

Sunday, January 24, 2010

On MS SQL how to restore a table or an object from backup file


On MS SQL how to restore a table or an object from backup file, the answer for this question is big NO from Microsoft, there is no direct option or feature to restore a table from MS SQL native backup, I’m not sure why still Microsoft is not adding this feature, but third party tools are providing this feature. Quest LiteSpeed is one of the third party tools providing this feature and also widely using backup tool for MS SQL. In this article I’m going to walk you through step by step how to restore a table from Quest LiteSpeed backup file.
I used Quest LiteSpeed version 5.0 for my test. To perform this recovery you need to open LiteSpeed console.
Go to Start/All Programs/Quest Software/LiteSpeed/LiteSpeed Console.
The below screen shot is the LiteSpeed console where you can manage the MS SQL backup, restore and other features like Log shipping as well. You can find the menu bar on top of the screen also on the side pan. On the side pan menu you can find the Object Level Recovery wizard button, click on it.





The below screen will open up, on middle of this screen you can find 2 steps 1st is Read a Backup File and 2nd is Object Recovery Actions. In order to recover an object you have to perform both the steps. Step 1 is reading the backup file to select specific object to recover. So click on the “Start the LiteSpeed Read Backup Wizard” (See below screen shot)





Start the LiteSpeed Read Backup Wizard will take you to the below screen where you can select the backup file from where your tables reside. Click Next button on the below screen


In the below screen you’ve to select the Backup source instance and backup files path. From the drop box you can select the source instance name and Click on the Add button on the right side and add the backup file path as below.

Then click Next button and the next window will list the backup file details, then click Next button.


The next window will show the preview restore script then Click Next button then click Finish.


Now you will come back to Backup Recovery Manager Window again and you can see list of objects on the backup, in the below screen you see the table with Type and Name columns and can see values Table and dbo.Table1 (Apologies for using just single object for this example), if you’ve views, stored procedures, functions and triggers in the database then all will be listed in the below table. You can select any of them which you need to be restored.


Right click on the table name [dbo.Table1] then you will get a sub menu where you can see all the actions to do next. Here you are going to click on the “Recover Table”


Another window will be popped up there you have to specify the SQL instance name, Database name and the table name which you are going to recover from the backup. You can choose either the existing instance & db name or a different, both will work. For the table name specify a new name for safer side as below.


On the above window there is an Advanced button if you click on it will show up the options for specifying the Temporary Directory, Ship Directory and Filegroup names, this will help if you want to keep this table in a separate File group on a specific file path.
Once you entered all the details then click OK button on the above window. Now LiteSpeed will restore the table on the specified database which you selected.
Now you can see the recovered table on the specific database on SSMS (see below screen shot). The ObjectReovery is the database which I used on this article and Dbo.Table1 is the table got backed up and Table_recovered is the table which recovered from the backup using Quest LiteSpeed tool.


Conclusion
Object level recovery can be done on MS SQL litespeed backup file but not from the MS SQL native backup files. Hope this article will help DBAs who are using Quest LiteSpeed for backup.

This http://support.microsoft.com/kb/321836 Kb link will explain how to recover a table from MS SQL native backup file.