How to Install SQL Server 2008 - A Step by Step Guide

A Step by Step guide to installing SQL Server 2008 simply and successfully with no prior knowledge
Developers and system administrators will find this installation guide useful, as will seasoned DBAs. It will teach you the basics required for a typical, problem-free installation of SQL Server 2008, allowing you to add other components later if you wish.
Remember to install the .Net Framework 3.5 Before you start the installation, you’ll need to install the .Net 3.5 Framework. This comes pre-installed on Windows 2008 Server, but for earlier versions of Windows, you’ll need to install it first. This is a straightforward pre-requisite and is usually included as part of the SQL Server 2008 installation. However, if you don’t know how to do this, or for some reason you need to download it, check out the guide Installing .Net Framework 3.5 for SQL Server 2008.
Once this Framework in installed you can commence the installation of SQL Server 2008.
STEP 1 : Copy the installation files First off I’d recommend you copy the entire directory structure from the SQL Server 2008 installation disc to the C: drive of the machine you are going to install it on.
Although this means you need to grab a cup of coffee whilst it’s copying, this has three advantages:

• It makes the installation process much faster than running it from CD/DVD once it gets started.
  
• It allows you to easily add or remove components later, without having to hunt around for the CD/DVD.
  
• If your media is damaged and a file won’t copy, you get to find out now, rather than halfway through the installation.
  

Here’s what my system looks like after the copy:
STEP 2 : Setup.exe Double click on the setup.exe file.
After a few seconds a dialog box appears:
This will disappear from the screen and then the main installation page appears:
STEP 3 : SQL Server Installation Center
Click on the Installation hyperlink on the left hand side of the screen:
STEP 4 : SQL Server Installation Center Click on the "New Server stand-alone installation" link on the right side of the screen:
The following dialog appears on the screen whilst the install program prepares for installation:
After a minute or so (the timing will vary according to your system), the following screen appears:
STEP 5 (optional) :
If any checks have failed, click on the Show details button or "View detailed report link" to find out the cause, correct it, then click on the Re-run button to perform the checks again. STEP 6 : Product key
If all checks have passed, click on the OK button. After a few moments, the option to select the edition and to enter the license key (or “product key”) will appear. Note that the product key box may already be populated, depending on which edition you have. Don’t enter the product key we’ve shown here, it won’t work on your system!:
STEP 7 : License Terms Enter the product key into the box, or choose the free edition if you're evaluating SQL Server 2008, and click on the Next button:
Click in the "I accept the license terms" check box, then click on the Next button again. STEP 8 : Setup Support Files
The following screen appears; click on the Install button:
The following screen will appear whilst Windows Installer prepares itself for the installation. This will take a short while:
After 30 seconds or so the dialog appears again:
STEP 9 : Setup Support Rules
If all is well, the following screen appears:
Click on the Next button again. STEP 10 : Feature Selection
Select the features you want to install.
At a minimum, the following are useful (I'd argue essential), but what you need will depend on your needs:
Click on the Next button. STEP 11 : Instance Configuration
After a short while the following screen appears:
For most installations, keep the default settings.
Click on the Next button. STEP 12 : Disk Space Requirements
This screen just tells you if you have sufficient disk space on the drive you’re installing to, and what’s going to be installed where.
Click on Next.


STEP 13 : Server Configuration
This step allows you to set up the service accounts that will be used to run SQL Server. If you have created Windows NT or Active Directory accounts for use with services, use these. If not, then just to get the installation up and working, use the built-in Network Service account for all three services listed (this account does not require a password).
This allows SQL Server to start up after installation. However, it can be easily changed later to another account through the Services applet (Control Panel -> Administrator Tools -> Services):
In addition, remember to change the Startup Type to Automatic, for all three services. This automatically starts the SQL Server database engine, SQL Agent and SQL Browser services when the server is re-booted. The first service runs the SQL Server database engines executable process. The other two services allow scheduled jobs to run after installation (and after a re-boot), and allow the SQL Server to be found by clients on the network.
Do not worry about changing the collation tab, unless there is a specific requirement for anything other than the default collation sequence. Finally, click on Next. STEP 14 : Database Engine Configuration – Account Provision
This screen allows you to set up database engine security.
Change the Authentication Mode to Mixed Mode unless you are certain you only need Windows-only authentication.

• Many third party applications rely on SQL Server logins to operate correctly, so if you are setting up a server for a third party application, rather than one developed in-house, enabling Mixed Mode authentication is a good idea.

If you pick Mixed Mode security, you must also enter a password for the sysadmin account (sa). Enter and confirm a secure password for the sa account and keep it somewhere safe. Do not give it to any one you do not want to have access to the SQL Server.
Note that you MUST also provide a Windows NT account on the local machine as a SQL Server administrator. If you do not want Windows system administrators to be able walk up to the box and login to SQL Server, create a new, local, dummy Windows user and add this account instead. Otherwise, add in the local administrator account, or your own Windows account on the domain in which the SQL Server will reside. STEP 15 : Database Engine Configuration – Data Directories
Click on the Data Directories tab.
Change the directories to specify which drives in your system will be used for the various types of database files. Generally it’s advisable to put the User database directory and User log directory on separate physical drives for performance, but it will depend on how Windows has been configured and how many disk drives you have available.
If you are installing on a single drive laptop or desktop, then simply specify:

Data root directory	C:\Program Files\Microsoft SQL Server
User database directory	C:\Data
User log directory	C:\Logs
Temp DB directory	C:\TempDB
Temp Log directory	C:\TempDB
Backup directory	C:\Backups

Do not click on the FILESTREAM tab unless you know you need to change these options, as it is not generally required for most installations, but can easily be changed by using sp_configure 'filestream_access_level', ''after SQL Server has been installed. Click on Next.
STEP 16 : Error Usage Reporting This screen simply asks if you want to send error information to Microsoft and can safely be skipped if you do not want to share any information.

Click boxes if you want to help Microsoft help you.
Click on Next again… STEP 16 : Installation Rules
This screen simply checks if there are any processes or other installations running which will stop the installation of SQL Server 2008.
Click on Next again – you’re almost ready to install:
STEP 17 : Ready to Install This screen summarises what you are about to install and gives you a last chance to cancel or change anything that’s wrongly configured:
Check that what’s being installed is what you want and then click on Install when you’re sure you want to start the installation process:
Installation Progress SQL Server 2008 will now install. How long it takes depends on the speed of your machine, what load it’s under, the installation media (CD is slower) and what you’ve chosen to install.
…More Installation Progress

... and Finally
Finally, the installation will complete:
...and the following dialog box will appear:
Click on OK, the machine will NOT reboot.
The following will appear:
…followed by:
Click on the Next button again... STEP 18 : Installation Complete
The following screen appears:
It may be worth clicking on the installation log at the top of the screen to check everything’s gone as expected. Not that this is MUCH smaller than the usual SQL Server installation log files of old. Finally, click on the Close button. The following dialog will appear:
Click on OK – your server will NOT re-boot at this point.
The dialog box will disappear and you will be returned to the Installation Center:
Click on the Close button (the “x”) in the top right of the screen.
Finally, manually reboot your machine to complete the SQL Server 2008 installation. Top Tips :
How to check that SQL Server 2008 has installed correctly Here are a short number of post-installation checks which are useful to perform after re-booting your new SQL Server. You don’t have to run these, and there are other ways to check, but they are very useful for non-DBAs to be sure that the installation is basically sound and a connection can be made to the new SQL Server before handing it over to someone else.
Check 1: Has the SQL Server Service Started? Check SQL Server 2008 has started.
Check 2: Does Management Studio Work?
Check Management Studio works by firing it up.
Click on NO when you see this dialog box:

Check 3: Can you run a basic query against the new SQL Server? Check SQL Server works by running a simple query from Management Studio:
Enter the query shown below and hit F5 to run it:
Check 4: Is SQL Server Agent Running?
Check SQL Server Agent is running for scheduled jobs. There should be a green arrow next to the SQL Server Agent database symbol (it’s small, you might have to look hard):
Check 5: Can SQL Server be seen from the Network? Check that the new SQL Server can be seen from another SQL Server on the same domain by running isql –L (or osql –L):
If you can’t see the new SQL Server in this list, check that the SQL Server Browser service is started on the machine where you have just installed SQL Server. Check 6: Has the TCP/IP network protocol library been enabled on the server?
If the browser service is started but you still cannot connect to the server, click on Start ->Programs -> SQL Server 2008 -> SQL Server Configuration Manager (on the server where SQL Server’s just been installed)
The SQL Server Configuration Manager window opens.
Click on the SQL Server Network Configuration node and expand it. In the example below, we have MSSQLSERVER (a base instance of SQL Server), and SQLEXPRESS showing as installed.
If in doubt, click on Protocols for MSSQLSERVER.
In the above screenshot, the TCP/IP network protocol library is disabled. We need to enable it in order that remote servers can talk to the newly installed SQL Server.

• A word of explanation : In most installations, Named Pipes can be ignored, unless there is a requirement for it. In virtually all environments, VIA can also be ignored as this protocol requires a special network card. Shared memory is the “local” protocol that SQL Server uses when talking to a client application on the same server as itself, for example when SQL Server Management Studio connects to it. It is usually best to leave this enabled.

You will need the TCP/IP protocol enabled if you need to connect to your new SQL Server from a remote client or another server via TCP/IP, which is what most networks use.
If it shows as DISABLED (above), double click on the TCP/IP protocol line, and the following window will appear:
Ensure that Enabled is set to Yes, and click on OK.
The following warning will appear:
Click on OK, and you will be returned to the Configuration Manager window, where TCP/IP will now be shown as enabled:
Go back to the Services applet, and re-start the MSSQLSERVER service so that the TCP/IP protocol can be used to connect to your new SQL Server. Then try to connect to it again from a remote machine.
If you have experienced problems with the previous connectivity tests, you should now be able to repeat at least some of them successfully.

SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

 

June 5, 2012 by pinaldave

If you have not read my SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor yesterday’s detailed primer on Resource Governor, I suggest you go ahead and read it before continuing this article. After reading the article the very first email I received was as follows:

“Pinal,

I configured resource governor on my development server and it worked fine with tests I ran. After doing some tests, I decided to remove the resource governor and as a first step I disabled it however, I was not able to drop the classification function during the process of the clean up. It was continuously giving me following error.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function myudfname. It is being used as a resource governor classifier.

Would you please give me solution?”

The original email was really this short and there is no other information. I am glad he has done experiments on development server and not on the production server. Production server must not be the playground of the experiments. I think I have covered the answer of this error in an earlier blog post.

If the user disables the Resource Governor it is still not possible to drop the function because it can be enabled again and when enabled it can still use the same function. Here is the simple resolution of the how one can drop the classifier function (do this only if you are not going to use the function).

The reason the classifier function can’t be dropped because it is associated with resource governor. Create a new classified function for your resource governor or just assign NULL as described in the following T-SQL Script and you will be able to drop the function without error.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)GOALTER RESOURCE GOVERNOR DISABLE
GODROP FUNCTION dbo.UDFClassifier
GO

I am glad that user asked me question instead of doing something radically different, which can leave the server in the unusable state. I am aware of this only method to avoid this error. Is there any better way to achieve the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

June 4, 2012 by pinaldave

Let us jump right away with question and answer mode.

What is resource governor?

Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server.

Why is resource governor required?

If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.

What will be the real world example of need of resource governor?

Here are two simple scenarios where the resource governor can be very useful.

Scenario 1: A server which is running OLTP workload and various resource intensive reports on the same server. The ideal situation is where there are two servers which are data synced with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. However, not everybody has the luxury to set up this kind of environment. In case of the situation where reports and OLTP transactions are running on the same server, limiting the resources to the reporting workload it can be ensured that OTLP’s critical transaction is not throttled.

Scenario 2: There are two DBAs in one organization. One DBA A runs critical queries for business and another DBA B is doing maintenance of the database. At any point in time the DBA A’s work should not be affected but at the same time DBA B should be allowed to work as well. The ideal situation is that when DBA B starts working he get some resources but he can’t get more than defined resources.

Does SQL Server have any default resource governor component?

Yes, SQL Server have two by default created resource governor component.

1) Internal –This is used by database engine exclusives and user have no control.

2) Default – This is used by all the workloads which are not assigned to any other group.

What are the major components of the resource governor?

• Resource Pools
• Workload Groups
• Classification

In simple words here is what the process of resource governor is.

• Create resource pool
• Create a workload group
• Create classification function based on the criteria specified
• Enable Resource Governor with classification function

Let me further explain you the same with graphical image.

Is it possible to configure resource governor with T-SQL?

Yes, here is the code for it with explanation in between.

Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server.

/*-----------------------------------------------
Step 0: (Optional and for Demo Purpose)
Create Two User Logins
1) ReportUser, 2) PrimaryUser
Use ReportUser login for Reports workload
Use PrimaryUser login for OLTP workload
-----------------------------------------------*/

Step 1: Creating Resource Pool

We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report ServerCREATE RESOURCE POOL ReportServerPoolWITH( MIN_CPU_PERCENT=0,MAX_CPU_PERCENT=30,MIN_MEMORY_PERCENT=0,MAX_MEMORY_PERCENT=30)GO-- Creating Resource Pool for OLTP Primary ServerCREATE RESOURCE POOL PrimaryServerPoolWITH( MIN_CPU_PERCENT=50,MAX_CPU_PERCENT=100,MIN_MEMORY_PERCENT=50,MAX_MEMORY_PERCENT=100)GO

Step 2: Creating Workload Group

We are creating two workloads each mapping to each of the resource pool which we have just created.
-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report ServerCREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;GO-- Creating Workload Group for OLTP Primary ServerCREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;GO

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------CREATE FUNCTION dbo.UDFClassifier()RETURNS SYSNAME
WITH SCHEMABINDINGAS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')SET @WorkloadGroup = 'ReportServerGroup'ELSE IF (SUSER_NAME() = 'PrimaryUser')SET @WorkloadGroup = 'PrimaryServerGroup'ELSE
SET @WorkloadGroup = 'default'RETURN @WorkloadGroupENDGO

Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.
-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------ALTER RESOURCE GOVERNORWITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);GOALTER RESOURCE GOVERNOR RECONFIGUREGO
Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.
-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)GOALTER RESOURCE GOVERNOR DISABLE
GODROP FUNCTION dbo.UDFClassifier
GODROP WORKLOAD GROUP ReportServerGroup
GODROP WORKLOAD GROUP PrimaryServerGroup
GODROP RESOURCE POOL ReportServerPool
GODROP RESOURCE POOL PrimaryServerPool
GOALTER RESOURCE GOVERNOR RECONFIGUREGO

I hope this introductory example give enough light on the subject of Resource Governor. In future posts we will take this same example and learn a few more details.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A List of Various SQL Server RTM and Service Pack Number

 

January 18, 2013 by pinaldave

A common question I receive is that how do user know which version user is using and what is the latest version available for the product. Here is something at this beginning of the year, check with your production server. See if you are using the latest version of the product or if you need to upgrade. If you need to upgrade you might want to plan it with your entire team. As I get this question often here is something I have already built and kept it with me. As soon as I receive an email with the question about the version, I right away send it to them.

Now onwards as I have blogged about this subject, in future question, I will like to this blog post. Please feel free to add more details if I have missed any.

Here are two of the methods, you can use to identify your SQL Server version:

Open SSMS and in a New Query Window run following T-SQL code.

1) SELECT@@version

2) SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The above query will return the version details of installed SQL Server.

Here is the list of the all the available RTM and Service Pack Numbers.

SQL Server 2012 Versions

Release	Product Version
SQL Server 2012 Service Pack 1	11.00.3000.00
SQL Server 2012 RTM	11.00.2100.60

SQL Server 2008 R2 Versions

Release	Product Version
SQL Server 2008 R2 Service Pack 1	10.50.2500.0
SQL Server 2008 R2 RTM	10.50.1600.1

SQL Server 2008 Versions

Release	Product Version
SQL Server 2008 Service Pack 3	10.00.5500.00
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	10.00.1600.22

SQL Server 2005 Versions

Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	9.00.1399

SQL Server 2000 Versions

Release	Product version
SQL Server 2000 Service Pack 4	8.00.2039
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 2	8.00.534
SQL Server 2000 Service Pack 1	8.00.384
SQL Server 2000 RTM	8.00.194

This method will help us find edition of the relational database. In future blog posts we will see the methods to identify the version of the Business Intelligence Services.

Reference: Pinal Dave (http://blog.sqlauthority.com)

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.

(Read more here http://ow.ly/gSP8A )