Archives For SQL Server 2008


How do I remove a named instance of sql server 2008 on the same mssql server?
If you have two instance in SQL Server 2008 and what are are the steps to remove one instance from SQL Server?

To remove a named instance of sql server 2008, follow these steps

1. Navigate to Control Panel->Add/Remove Programs

2. Select Microsoft SQL Server 2008

3. Select Change/Remove

4. Select Remove

5. At Select Instance spefify the instance of SQl Server to modify

Select the instance of SQl Server to remove. To remove management tools and shared features only, select “Remove shared features only” and then click next

uninstall named instance sql 2008

uninstall named instance sql 2008

Removal progress will go through and remove named instance of sql server 2008 on the same mssql server


How to find SQL Server configuration settings in SQL Server?

Configuration files can be found at :
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20140321_112944\ConfigurationFile.ini

SQLConfigurationPath


Source:http://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

You can download “Microsoft Kerberos Configuration Manager for SQL Server” from here

Microsoft Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.

Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network.  To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain.  In addition, many customers also enable delegation for multi tier applications using SQL Server.  In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.   It can perform the following functions:

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.
Supported Operating System

Windows 7, Windows 8, Windows Server 2008 R2 SP1, Windows Server 2012

The following are required on the machine where the Kerberos Configuration Manager for SQL Server is launched:

    • .Net framework 4.0 or higher

To Install:

  1. Download the 32bit or 64bit version of the Kerberos Configuration Manager installer that matches your computer’s OS architecture.
  2. Click Open to start the installation immediately or click Save to save the installation .msi file to disk and install it later.
  3. Accept the license term of this tool.
  4. Click Next to complete the installation.

To Launch the Tool:

  1. After the installation is complete successfully, double click the KerberosConfigMgr.exe  to launch the application.

To Generate SPN List from Command Line:

  1. Go to command line.
  2. Switch to the folder where KerberosConfigMgr.exe is.
  3. Type KerberosConfigMgr.exe -q -l
  4. For more command line option, type KerberosConfigMgr.exe -h

To Save a Server’s Kerberos Configuration Information:

  1. Connect to the target windows server.
  2. Click on Save button on the toolbar
  3. Specify the location where you want the file to be saved at.  It can be on a local drive or network share.
  4. The file will be saved as .XML format.

To View a Server’s Kerberos Configuration Information from Saved File:

  1. Click on the Load button on the toolbar.
  2. Open the XML file generated by Kerberos Configuration Manager.

To Generate a Script to Fix SPN from Command Line:

  1. Click on the Generate button for the SPN entry.
  2. The generated script can be used by a user who has privilege to fix the SPN on the server.

To See the Log Files for this Tool:

  1. By default, one log file is generated in the user’s application data folder.

To Get Help:
Option 1: Hover the mouse cursor over the command for tooltip.
Option 2:  Run KerberosConfigMgr.exe –h from command line
Option 3: Click the Help button in the toolbar.


Where I can download Remote Blob Storage installation files for SharePoint 2010?
You can download rbs_x64.msi file from : http://go.microsoft.com/fwlink/p/?LinkID=165839&clcid=0×409

How do I install and configure RBS for SharePoint?

1. Download Remote Blob Storage from above link
2. Before you install Remote Blob storage, make sure SQL server 2008 enabled FILESTREAM. To enable FILESTREAM

Start | All Program | Microsoft SQL Server 2008 R2 | Configuration Tools | Configuration Manager
Click on SQL Server Services | SQL Server | Properties

Enable FILESTREAM for BLOB Storage

3.  Enable following services

Enable File stream for Transact-SQL Access
Enable File stream for file I/O streaming access
Allow remote clients to have streaming access to Filestream data

Enable Filestream for SharePoint BLOB storage

4. Open SQL Management Studio and performing following task

use WSS_Content If not exists
(select * from sys.symmetric_keys where name = N‘##MS_DatabaseMasterKey##’)
create master key encryption by password = N‘Pa$$word’


image

5. Next we need to enable FileStreamProvider

Filestream feature is disabled, how to enable

use WSS_Content if not exists

(select groupname from sysfilegroups where groupname=N‘RBSFilestreamProvider’)

alter database WSS_Content add filegroup RBSFilestreamProvider contains filestream

If you get error message saying “ FILESTREAM Feature is Disabled” run the following query

FILESTREAM Feature is Disabled

Configuration option 'filestream access level'

6.  Next run the following query.  This will create a folder in E called Blobdata. This can be different in your case

use WSS_content alter database WSS_Content add file

(name = RBSFilestreamFile, filename = ‘E:\blobdata’)

to filegroup RBSFilestreamProvider


Create RBS File Stream File Folder

8. Now let’s go and install RBS. Best practice would be to install using command prompt  but it won’t give you any steps about what’s going on.
I had problem using Command and I installed using the GUI.  If you want to install using command here are the steps

(open command prompt as administrator).
I placed RBS installation file at C:\RBS folder Navigate to RBS folder using your Command Prompt

image

9.  Enter the following code

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true
FILEGROUP=PRIMARY DBNAME=”WSS_Content” DBINSTANCE=”YourServer”
FILESTREAMFILEGROUP=RBSFilestreamProviderFILESTREAMSTORENAME=FilestreamProvider_1

Installing RBS Using Command Prompt

10. It won’t give you any indication about the installation. To see if its successfully installed Blob storage, just go to the folder and look for the log file

11. Installing RBS using GUI.  Double click on RB_X64.msi

12.  Welcome to the installation wizard for Remote Blob Storage wizard pops up. Setup helps you install, modify or remote remote Blob storage.

To continue, click Next

installation wizard for Remote Blob Storage

12. Install SQL Remote Blob storage : Licence Agreement

Install SQL Remote Blob storage : Licence Agreement

13. Install SQL Remote Blob Storage: Registration Information, the following information will personalize your blob installation

SNAGHTMLe57dbb

14.  Install SQL Remote Blob Storage : Feature Selection

Select the program features you would like to install and select the installation path where you want to install the program

Install SQL Remote Blob Storage : Feature Selection

15. Blob Storage Database Connection. You can test your connection against the database
which your setting blob storage by hitting test connection.

Blob Storage Database Connection

16. SQL Remote Blob Storage: Database Configuration

Enter information to configure RBS database settings

Blob Storage Database Configuration

17. Filestream Blob Store: Enter Filestream blob store configuration Information

image

18. Maintainer Task. Enter information to optionally schedule the maintainer task

During installation, the windows task scheduler window will apper. Use this to set scheduled task properties.

image
19. Remote Blob Storage Client Configuration

Remote Blob Storage Client Configuration

20.  Install Remote Blob Storage.  The program features you selected are being installed.

Please wait while the installation wizard installs remote blob storage. This may take several minutes.

image

21.To ensure that the installation was successful, open up the Content DB | tables

Ensure the blob installation in SharePoint

22. Now open your SharePoint 2010 Management Shell and enter the following scripts
$cdb = Get-SPContentDatabase –WebApplication http://vsp2010be
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed() – This should result in True. Else the next command will fail.
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

 

image

23. Now set the minimum threshold so that only files larger than a 1 MB will be placed in our blobdata fodler

$rbss.MinimumBlobStorageSize=1048576

image

24. Go head and upload a document which is larger than 1 MB.  This document should end up in the blobdata folder

I uploaded 8 GB of PDF file and it end up in the blobdata folder

Files moved to Blobdata Folder


Installing the FILESTREAM blob store failed with error message:
Access to the registry key
‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Remote Blob Storage’ is denied.
Setup will continue the installation”

 

Installing the FILESTREAM blob store failed with error message

 

The reason your getting this message because of Disable User Account Control (UAC)

Open your Control Panel in Windows 2008 Server and type in UAC

 Disable User Account Control

 

Change your UAC control

image

 

Restart the computer.

SQL Server Virtual Labs

December 12, 2011 — Leave a comment

Where to find SQL Server 2008 Virtual Labs? You can find SQL Server Virtual labs at : SQL Server Virtual Labs

Microsoft SQL Server 2008 Virtual Labs

Test drive Microsoft SQL Server 2008 in a virtual lab. See why SQL Server 2008 is at the heart of a comprehensive data programmability platform that enables you to access and manipulate business-critical data from a variety of diverse devices, platforms, and data services across the enterprise. Virtual labs are simple, with no complex setup or installation required.

SQL Server 2008 R2 Virtual Labs

SQL Server 2008 Virtual Labs


How to optimize SQL Server 2008 to get best performance for SharePoint 2010 farm?

Reference: http://technet.microsoft.com/en-us/library/hh292622.aspx

1. Use a dedicated server for SQL Server 2008
2. Configure specific SQL Server 2008 settings before you deploy SharePoint Server 2010

- Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server

auto-create statistics-on-a-SQL-Server

- Set max degree of parallelism (MAXDOP) to 1 for SQL Server instances that host SharePoint Server 2010 databases to ensure that each request is served by a single SQL Server process.

changing max degree of parallelism

Reference: http://msdn.microsoft.com/en-us/library/ms181007.aspx

3. Harden the database server before you deploy SharePoint Server 2010
4. Configure database servers for performance and availability
5. Design storage for optimal throughput and manageability
6. Proactively manage the growth of data and log files
7. Continuously monitor SQL Server storage and performance
8. Use backup compression to speed up backups and reduce file sizes

Reference: http://technet.microsoft.com/en-us/library/hh292622.aspx


Overview  : Download SQL Server 2008 Services Pack 3

Microsoft SQL Server 2008 Service Pack 3 (SP3) is now available for download. While keeping product changes contained, we have made significant investments to ease deployment and management of Service Packs. A few key enhancements for customers in Microsoft SQL Server 2008 Service Pack 3 are :

  • Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
  • In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
  • Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
  • Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
  • Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
  • Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

Microsoft SQL Server 2008 Service Pack 2 is not a prerequisite for installing SQL Server 2008 Service Pack3. These packages may be used to upgrade any of the following editions of SQL Server 2008:

  • Enterprise
  • Standard
  • Evaluation
  • Developer
  • Workgroup

Download SQL Server 2008 Services Pack 3


What’s currently going on on the SQL Server, who is executing which query or fetches a few thousands of rows and slowing down the server with it?
With this Transact-SQL script you can list all processes with their SQL statements.

Additional you get the cummulative values of IO / CPU usage and the row count of the last statement execution.

Script Source: http://gallery.technet.microsoft.com/scriptcenter/Current-processes-and-d9b4c8d9

End Results in SQL Server

Checking SQL Server Current Status


The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

  • Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
  • Determines if the configurations are set according to the recommended best practices
  • Reports on all configurations, indicating settings that differ from recommendations
  • Indicates potential problems in the installed instance of SQL Server
  • Recommends solutions to potential problems

Download SQL Server 2008 Best Practices Analyzer

Microsoft SQL Server 2008 R2 BPA


Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.

Overview

Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 in preparation for upgrading to SQL Server 2008. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

Download Microsoft SQL Server 2008 Upgrade Advisor


1. Open notepad and save the file as connection.udl

connection

2. Now you will have a file in called connection.udl

udl

3. Open connection.udl file and enter your server name and user name/password to test your SQL connection

test-sql-connection


Source: How to Connect SQL Servers to the SAN

How to Connect SQL Servers to the SAN

The term SAN gets misused a lot because it really means Storage Area Network – the communication pipelines between your server and a magic black box called a SAN controller.  That controller is the configurable hardware that manages RAID levels, caching, and more.  Here, we’re talking about how we plug your SQL Server into the network (SAN) itself, and how it gets there is called pathing.  Here’s my articles on it:

Source: How to Connect SQL Servers to the SAN


How do you give permission to a database in SQL server to a QA or someone who wants to have read only access?
In SQL server 2008, if you want to give read only access to the database for a person, do the following steps

1. Create a new login which is going to have read only access to the database
Right click and click on New Login

image

2. Add the users from Active Directory

Adding users to SQL server

3. Click on User Mapping and Select the database which you wan to give read access below Users Mapped to this login:
and select Database Role membership as public and db_datareader

Read Only Access to SQL Database

Above setup will allow the users to have database reader access to SQL server


What happen to open table in SQL 2008 and how do I open a table in SQL 2008?
In SQL 2008, to open a table you need to Right Click table which you want to open and select edit top 200 rows

Open table in SQL 2008

The reason it got changed this way in SQL 2008 is that in previous version users are opening hug tables and it slows down the SQL performance.
In this way it will only allow you to open top 200 rows.