Adventure Works for SQL Server 2012

On March 16, 2012, in MS SQL Server, Technical Knowledge, by Soheib
2

This tutorial helps you to get Adventure Works database added to your SQL Server 2012. First thing we need to do is to download the sample database from Microsoft’s website:

http://msftdbprodsamples.codeplex.com/releases/view/55330

On the website, you will see a link to AdventureWroks2012 Data File:

image

After you have download it, follow these steps:

1. Copy/Move the Data File to the SQL Server DATA directory.

The directory structure for a SQL Server 2008 R2 named instance that includes the Database Engine, named “MyInstance”, and installed to the default directories would be as follow:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MyInstance\

Note: I am using SQL Server 2012 Express and I have changed my default directory for DATA

 

2. Open SQL Server Management Studio (SSMS) and Right click on the Database folder then click on Attach:

image

3. A window will open to add your MDF file. Click on Add button:

image

4. Since you copied Adventure Work Data File (.MDF) to the default DATA directory, it will appear under the DATA directory. Select AdventureWorks2012_Data.mdf and click OK:

image

5. A log file could be automatically added for you. Since this is the first time we are adding this Data File, there is no Log file. Therefore, select the Log entry and click on Remove to delete it. Then click on OK to proceed.

image

6. You have now successfully added Adventure Works to your Database and you can see it listed under your Databases folder in your Object Explorer:

image

7. You can also see that SQL Server added a log file for your new .mdf file which you just added:

image

Tagged with: •
 

SQL Server 2012 Express Installation Tutorial

On March 15, 2012, in MS SQL Server, Technical Knowledge, by Soheib
9

Before we begin the installation process, it is important that you choose the correct version of SQL Server Express. To learn more about which version to download, read the following article and download the right SQL Express version:

SQL Express 2012 Versions

After downloading the correct version, make sure that you have the right hardware and software requirements.

Environment Check

  • Supported OS: Server 2008 R2 SP1, Server 2008 SP2, Windows 7 SP1, and Vista SP2.
  • Processor:
  • Minimum for x64 installations: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. Processor Speed: 1.4 GHz
  • Minimum for x86 installations: Pentium III compatible or higher. Processor Speed: 1.0 GHz
  • Memory: Minimum: 512 MB

If you do not have the minimum requirement, you will be prompted with a message box similar to this:

(I was trying to install SQL Server 2012 Express on Windows 7 which did not have Service Pack 1 installed.)

image_thumb38_thumb

You can also perform System Configuration Check using a tool provided by the SQL Server 2012 Express application installation. Run the executable file after you have downloaded. Installation files begin to be extracted to a temporary folder, then the below window will appear. From the Planning menu on the left, choose System Configuration Checker:

image_thumb37_thumb

 

1. If you pass the minimum requirements, then you can proceed with the software installation. From the Installation menu on the left side, choose New SQL Server stand-alone installation or add features to an existing installation:

image

2. Setup will perform a quick system check. You can see below that one of the conditions failed and asks for a system reboot. This is because I have installed Window 7 Service Pack 1 (using Window Update) but did not restart my computer:

image

3. If you also required to do a reboot, then you have to start over the installation process from the beginning. In this step you have to accept the License Terms to continue:

image

4. Another quick check is done and this time the system passed all:

image

5. We can select the features that we want to install. Note that in this example, I have downloaded SQL Server 2012 Express with Advanced Services. This includes the Reporting Services:

image

6. Name the instance of your new SQL Server Express. I am also changing the Instance root directory:

image

7. You can define separate account for each service. I am leaving everything as the default:

image

8. You can also specify your Database Engine Collation. I am leaving as the default:

image

9. Choose the authentication type. It is recommended to choose Mixed mode for better security. You can also change this at a later time. Read this article to learn how to: Change SQL Server Authentication mode

If you choose mixed mode, then you have to enter a password for the SQL Server system administrator (sa) account:

image

10. If you ticked in the feature’s section to install Reporting Services, then here it will ask you whether to have the default configuration or do installation only and do Reporting Services configuration manually later. For this exercise, I am leaving as the default – to do both installation and configuration. Read the following article if you want to learn how to do configuration manually:
Installing and Configuring Reporting Services

image

11. The system will now continue with installation:

image

12. Finally, the last screen shows the installation process is completed successfully:

image

13. It may ask you to restart your computer:

image

14. We can see now that SQL Server 2012 Express with other tools installed under All Programs:

image

15. We can run the SQL Server Management Studio (SSMS) and see that we have already two databases installed. These are related to the Reporting Services.

image

16. We can also verify that the Reporting Services is running by typing the URL: http://localhost/Reports_SQLExpress

image

 

All done. Note that Reporting Services when access by IE9, will prompt you to enter username and password. To learn how to resolve this issue, please read the article:

SSRS Prompt for username and password

Reporting Services may also give you some User Access Control authorization error (UAC). To learn how to resolve this issue, read the following article:

SQL Server Reporting Services User Access Control (UAC)

Tagged with: • •
 

SQL Server 2012 Express

On March 15, 2012, in MS SQL Server, Technical Knowledge, by Soheib
3

“SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications. Download SQL Server 2012 Express RTM, which includes SQL Server Express LocalDB, a new lightweight deployment option for Express…”   Microsoft

 

The express version of SQL Server is really a product that many developers can benefit from specially that it is reasonably powerful for a free version. Nevertheless, it has some limitations:

  • Maximum database size 10GB
  • Maximum usage of Ram 1GB
  • Single physical CPU
  • No SQL Server Agent (job scheduler)
  • Profiler tool is not included

SQL Server Express comes in different versions depending on your needs. One of the latest add-on is LocalDB which is yet a lighter version of Express. Here are the differences between each version as Microsoft describe:

LocalDB (MSI installer)

New to the Express family, LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Visual Studio, other Database Development tools or embedded with an application that needs local databases.

Express (containing only the database engine)

The core Express database server. Use this if you need to accept remote connections or administer remotely.

Express with Tools (with LocalDB, includes the database engine and SQL Server Management Studio Express)

This package contains everything needed to install and configure SQL Server as a database server. Choose either LocalDB or Express depending on your needs above.

SQL Server Management Studio Express (Tools only)

This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, etc. Use this if you already have the database and only need the management tools.

Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)

This package contains all the components of SQL Express. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

 

Download

You can download SQL Server 2012 Express from the following link: http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

 

Installation Tutorial

You can view a step-by-step installation guide for SQL Server 2012 Express from the following link:

SQL Server 2012 Express Installation Tutorial

Tagged with: •