Transcription

Microsoft SQL Server Installation GuideVersion 2.1For SQL Server 2012January 2013Copyright 2010 – 2013 Robert Schudy, Warren Mansur and Jack PolnarPermission granted for any use of Boston University

Table of ContentsIntroduction . 3SQL Server Editions . 4Downloading SQL Server Installer . 5MSDN Academic Alliance . 5Directions to Download SQL Server Developer Edition from MSDNAA . 5Directions to Download SQL Server Express Edition from Microsoft . 6Installing SQL Server 2012 Developer Edition . 79.Feature installation . 8Minimum Required Features List for CS 669 . 8Detailed Explanations . 811.Instance Configuration . 1214.Database Engine Configuration . 1515.Analysis Services Configuration . 1716.Error Reporting, Installation Configuration Rules, & Ready to Install . 18Installing SQL Server 2012 Express Edition . 206.Feature installation . 20Minimum Required Features List for CS 669 . 20Detailed Explanations . 207.10.Instance Configuration . 22Database Engine Configuration . 24Installing Service Packs . 25Working with SQL Server . 26Starting & Stopping SQL Server (optional) . 26Logging into SQL Server using SQL Server Management Studio . 27Using SQL Server Management Studio . 28Uninstall SQL Server or Change Features . 30Uninstalling SQL Server . 30Change SQL Server Features and Components . 302

IntroductionThese instructions are used by students enrolled in the Master of Science in Computer InformationSystems and other Computer Science Department programs in both on-campus and online programs. Insome places these instructions say that you should contact your instructor. Online students shouldnormally contact their facilitator first.The document begins with discussion SQL Server editions and how they are utilized in our courses,downloading SQL Server from BU MSDNAA or from Microsoft directly, and installing SQL Serveritself. We continually update this document; please let us know of problems you encounter or questionsnot answered.The examples in the main document are for the Microsoft Windows family, including Windows 2000,XP, Vista, and System 7.These setup instructions are for SQL Server 2012 Developer and Express Editions, but can be used ininstalling other versions from 2008 and 2012 families. If you cannot install SQL Server on your ownmachine, and you have ready access to a SQL Server installation you may be able to use it. All of theexercises in CS669 can be completed using SQL Server 2005 or later. Some of the exercises in METCS779 require Oracle, although SQL Server will work for most. CS 779 students may wish to use bothSQL Server and a recent version of Oracle, such as 11gR2. CS779 students wishing to explore advancedtopics as part of their term project should look to install the developer or enterprise editions. For CS699you will want to use SQL Server 2012 Developer Edition (2008 or 2008R2 is ok as well). The expressedition is not compatible for CS699, so if you are planning to take this course in the future, you will wantto install the Developer Edition.3

SQL Server EditionsSQL Server is released in several editions. To simplify your selection, we will focus on the Developerand the Express editions. For both CS 669 and CS 779 we recommend that you use the DeveloperEdition, it is required for CS699. The Developer Edition has the same full feature set as the EnterpriseEdition with the exception of the license. Production environments cannot be implemented using theDeveloper Edition. Please refer to the SQL Server 2012 Licensing Quick Reference Guide for additionalinformation. erver/how-to-buy.aspxThe Developer Edition is especially useful for those students who plan investigate advanced SQL Servertopics as part of their CS 779 term project. It is also the version that you will use for CS699. You candownload the Developer Edition from the MET BU MSDNAA. Our installation instructions explainwhich features you may find useful for either course.If you have difficulty downloading and installing the Developer Edition, the Express Edition can be usedfor all of the CS 669 and most of CS 779 assignments, but not for CS699. The Express Edition is a freeversion of SQL Server which is a smaller version that places limits on the size of the database, computingcapacity and feature set. For some of the MET courses, we have found that the Express Edition issuitable on the most part, and has advantages of a much smaller load on the computer and simplerinstallation.For additional details on the features supported by both the Developer (Enterprise), Express as well asother editions please refer to the Features Supported by the Editions of SQL Server 2012 which can befound here: spx4

Downloading SQL Server InstallerMSDN Academic AllianceMET College is a member of the MSDN Academic Alliance, which allows faculty, graduate andundergraduate students currently enrolled in MET courses to obtain certain Microsoft products free ofcharge. You can obtain SQL Server Developer free of charge from the Microsoft Developer NetworkAcademic Alliance (MSDNAA) Program.By the first day of class your instructor will submit your BU email address to Microsoft to enroll you inthe program for the current semester. You will receive an email from the MSDNAA E-Academy LicenseManagement System (ELMS) from the address: @e-academy.com. The email will provide you with ausername and password, and direct you to the MSDNAA site.FAQ and basic information are at: liancesoftware-center/If you do not receive your email by the end of the first week, first check your junk email folder and thenplease follow the instructions at liance-softwarecenterDirections to Download SQL Server Developer Edition from MSDNAA1. You will receive an email from MSDNAA with your username and password as well as the link forthe MSDNAA.2. Click on Start Shopping link.3. You should see a link for Microsoft SQL Server 2012 within the Popular or Servers categories, clickon it.4. Look for Microsoft SQL Server 2012 Developer 32/64-bit (English) and click Add to Cart5. Sign in with your MSDNAA user name and password6. Once your order is processed, click on the Start Download link7. Follow the instructions as outlined:8. In step 1 you will download is the Secure Download Manager (SDM) installation file. Once SDM isdownloaded and installed, download the .SDX file and open it (step 3 above). The SDM will startthe download of the SQL Server Installer. Note that it is several gigabytes in size and be sure to notethe download destination under the download progress bar.5

9. Once the download is complete go to the destination folder (i.e. downloads folder on your computer).The installation file is an ISO image file, and you will need to burn the image to a DVD.If your computer does not have the capability to burn DVD images from an ISO files, please followthe following links to install a disk burning program or virtual mounting demic-alliance-software-center/#iso as well asftp://ftp.e-academy.com/pub/docs/Working with Image Files.pdfDirections to Download SQL Server Express Edition from Microsoft1. Go to TAV2/enus/default.aspx2. Depending on what type of Windows environment (32bit or 64bit) which you are running, under thedropdown, choose Express with Tools. Note: if you are not sure if you have a 64 bit environment,32 bit version will work fine on both.3. Click on Download, and the installation file will save to your default downloads folder6

Installing SQL Server 2012 Developer Edition1. Insert the SQL Server 2012 Developer installation DVD and Run Setup.Exe to start the setup ofSQL Server 2012 Developer, if prompted, give administrative permissions.2. Once the SQL Server Installation Center launches choose Installation tab (second from the right).3. In most cases you will want to run a New SQL Server New SQL Server stand-aloneinstallation, but other options are available, for example if you have SQL Server 2005 installed,you have an option to update.4.5.6.7.8.(CS779Note) If you are configuring the installation for CS779 and you might want to explorefailover clustering. Note that you can go back and install this component at a later time.Setup will check and if needed install Setup Support Files. Click OK when complete.On the Product Key page click Next.On the License Terms page, check the box next to “I accept the license terms” and click Next.Setup will check again and if needed install Setup Support Files. Click Next when complete.Setup Role: Select SQL Server Feature Installation and click Next.(CS779Note) If you are configuringthe installation for CS779 and youmight want to explore PowerPivot forSharePoint. Note that you can goback and install this component at alater time.7

9. Feature installation: Select the components of SQL server to install on your computer.Minimum Required Features List for CS 669You will need to choose at least two of these feature components in order to use SQLServer for CS669.o Database Engine Serviceso Management Tools- BasicFor CS699 & CS779 in addition to what is listed above please review these descriptions tosee which features you might be interested in for advanced topics for the term project.Detailed Explanations:a. Instance Features:Database Engine Services: This installs SQL Server engine (the core service forstoring, processing and security data) on your system. This is required for bothCS669, CS699 and CS779.SQL Server Replication: Allows for having synchronized copies of thedata in two different locations. For example, this can be used in datadistribution, synchronization, load balancing and disaster recovery.a. (CS669) Not needed for this courseb. (CS699) Not needed for this coursec. (CS779) This is a worthy subject for a term project, and can beinstalled at a later time. Note that you may need two separatesystems.Full Text and Semantic Extractions for Search: In some databaseimplementations where there are a lot of text data that needs to besearched, full text search allows for additional indexing for faster dataretrieval.a. (CS669) Not needed for this courseb. (CS699) We suggest that you install this component. It may notbe used directly, but may be useful to explore on your own.c. (CS779) This is a worthy subject for a term project, and can beinstalled at a later time.Data Quality Services: Enables you to discover, build, and manageknowledge about your data. You can then use that knowledge to performdata cleansing, matching, and profiling.a. (CS669) Not needed for this courseb. (CS699) Not required, however we suggest that you install thiscomponent. It may not be used directly, but may be useful toexplore on your own.c. (CS779) This is a worthy subject for a term project, and can beinstalled at a later time. Make sure to select Data Quality Clientunder Shared features.8

Analysis Services: Provides support for analytical processing (OLTP) and datamining, such as when you set up a data warehouse and want to perform some adhoc queries against cubes.(CS669) Not needed for this course.(CS699) This is a required component service that will be used in datamining exercises.(CS779) If you plan to do a Data Warehouse project or data mining youwill need this.Reporting Services: Allows for creation of Reports based on data in OnlineTransaction Processing (most common databases) and Data Warehouses. Thereports can be in the form of tables, charts, and other formats.(CS669) Not needed for this course(CS699) Not needed for this course.(CS779) This is a worthy component to explore as part of a term project.b. Shared Features:Reporting Services: Allows for creation of Reports based on data in OnlineTransaction Processing (most common databases) and Data Warehouses. Thereports can be in the form of tables, charts, and other formats.(CS669) Not needed for this course.(CS699) Not needed for this course.(CS779) This is a worthy component to explore as part of a term project.Data Quality Client: Not needed unless you are planning to try Data QualityServices for CS779SQL Server Data Tools: Formerly Business Intelligence Development studio, Aversion of Visual Studio with some DBMS components for Analysis services,and development tools, etc.(CS669) You will probably not need this component, but it isrecommended that you install it for future courses as it’s an integral partof SQL Server.(CS699) This is a required component service that will be used in datamining exercises.(CS779) This component is needed for most advanced topics so it shouldbe installed.Client Tools Connectivity: components to communicate between clients andservers.(CS669) Not needed for this course(CS699) Not needed for this course(CS779) Might be needed for some advanced components, check ifneeded for what you are planning to do for the term project, can be addedlater.9

Integration Services: This is needed for data warehouse ExtractionTransformation Loading Processes when data needs to be transformed and loadedinto the database.(CS669) Not needed for this course.(CS699) This is a required component.(CS779) If you plan to do a DW project or data mining you will need thiscomponent.Client Tools Backwards Compatibility: This is not needed for the courses in thisprogram, but in production environments if you connect to older versions of SQLServer from management tools this option is needed.Client Tools SDK: Additional Software Development Kit with recourses fordevelopers.(CS669) Not needed for this course(CS699) Not needed for this course(CS779) You might want to do some research to see if there might beanything here of interest for a term project, especially if you enjoyprogramming in Object Oriented languages.Documentation Components: This is the documentation for SQL Server. Notethat this information is also available online.Management Tools Basic & Complete: you will need this to work with SQLServer, this is the GUI interface that include components such as the GUI Queryinterface as well as components for advanced topics such as analysis andintegration services as well as the database tuning advisor. It is required that youinstall the Management Tools Complete for all courses.Distributed Replay Controller and Client. An advanced topic component. Workssimilar to SQL Server Profiler to capture traces against upgraded environments.(CS669) Not needed for this course(CS699) Not needed for this course(CS779) You might want to do some research to see if there might beanything here of interest for a term project.SQL Client Connectivity SDK: Additional Software Development Kit withrecourses for developers.(CS669) Not needed for this course(CS699) Not needed for this course(CS779) You might want to do some research to see if there might beanything here of interest for a term project.Master Data Services: A platform for integrating data from disparate systemsacross an organization into a single source of master data for accuracy andauditing purposes.(CS669) Not needed for this course(CS699) Not needed for this course(CS779) You might want to do some research to see if there might beanything here of interest for a term project.10

c. Shared Features Directory: Note the paths where SQL server will install the sharedcomponents (default is Program Files folder within C drive.)10. Installer will verify Installation Rules, click Next when prompted.11

11. Instance Configurationa. Generally you can leave the Default Instance. The Named instances would be used if youwant to create multiple instances of SQL Server on the same machine. If you areinstalling SQL Server for CS779 this might be a worthy topic to explore for the termproject.b. Choose the installation path for SQL Server (default is Program Files folder within Cdrive.)12. Review the Disk Space Requirements and click Next when prompted.12

13. Under Server Configuration set Configuration set both SQL Server Agent, Analysis Services(if installed) and SQL Server Database Engine to Manual (unless you want it to run all the timewith your system which will use up a lot of system recourses when you are not using the SqlServer). These are services that run components of SQL Server.Please note the following:a. SQL Server Agent is used for running scheduled jobs, such as backups, scheduled sqlscripts and db maintenance. If this was a production environment you would want thisservice set to automatic.b. You will need SQL Server Database Engine to run SQL Server. Since DBMS uses alot of system recourses, we would recommend running it manually when you need it.c. If you installed other components for SQL Server for advanced topics, you should alsoset them to manual so that they don’t run on system startup.d. SQL Server Browser can be left disabled.e. You will need SQL Server Analysis Services for CS699; this runs the back-end datamining components.f. It is strongly recommended to set the Account name for services, especially the SQLServer Analysis Services to NT AUTHORITY\LOCAL SERVICEThe image above are the defaults, we suggest that you set Account Name to NTAUTHORITY\LOCAL SERVICE13

Check the collation tab at the top. For our purposes this can be left at defaultSQL Latin1 General CP1 CI AS which is Latin1-General case insensitive. This drives howSQL server recognizes commands, for example you can choose a different language or set it to becase sensitive. Some applications require for you to choose a specific collation. You can clickCustomize to change it. Click Next when done.14

14. Database Engine Configurationa. Account Provisioning:Choose the authentication mode for the system. Windows authentication will useyour windows account privileges to connect to SQL Server. We highlyrecommend using Mixed Mode so that there is an additional built in SAaccount with a separate user name and password as well as your built in windowsaccount.Make sure to add users (such as your account) to SQL ServerAdministrators (click on Add Current User) if it is not already there.These accounts will allow you to log into SQL Server.Note that the server itself does not need these accounts and runs as a servicewhich you specified in previous step.b. You can leave Data Directories to defaults. Data Directories can be changed if you havea multiple disk environment and for performance want to separate out where differentparts of the DBMS go. For example, in production environments the LOG componentsshould go on a separate disk array, which will improve performance of the system.15

c. If you are installing SQL Server for CS779 you might want to enable FILESTREAM ifyou plan to explore large file types such as Binary language objects (BLOB). We suggestthat this feature is enabled for CS699.16

15. Analysis Services ConfigurationIf you have selected Analysis Services component (i.e. for CS699 or CS779) you will be presentedwith the following screen.Select the Multidimensional and Data Mining ModeClick Add Current UserClick Next17

16. Error Reporting, Installation Configuration Rules, & Ready to Install17. Click Next through the next two screens It is up to you to select Error and Usage Reporting, clicknext.18

Review selected features and click Install, Installation will begin, this will take some time.This will take some timeOnce the installation is complete, if available run a service pack installation. You can check ifthe latest service pack is available from the SQL Server web site.19

Installing SQL Server 2012 Express Edition1. Please note that the Express Edition is only suggested for CS669. It is not recommended forCS699 or CS779.2. Run SQLEXPRWT x64 ENU or SQLEXPRWT x32 ENU file which you have downloadedfrom Microsoft to start the setup of SQL Server Express 2012. The setup program will initiatethe setup which may take several minutes.3. Once the SQL Server Installation Center launches choose New SQL Server New SQL Serverstand-alone installation, but other options are available, for example if you have SQL Server2005 or 2008 installed, you have an option to update to the 2012 version.4. On the License Terms page, check the box next to “I accept the license terms” and click Next.5. Setup will check and if needed install Setup Support Files. Click OK when complete.6. Feature installation: Select the components of SQL server to install on your computer.Minimum Required Features List for CS 669You will need to choose at least two of these feature components in order to use SQLServer for CS669.o Database Engine Serviceso Management Tools- BasicDetailed Explanations:a. Instance Features:i. Database Engine Services: This installs SQL Server engine (the core service forstoring, processing and security data) on your system. -Requiredii. SQL Server Replication: Allows for having synchronized copies of the data intwo different locations. For example, this can be used in data distribution,synchronization, load balancing and disaster recovery.20

b. Shared Features:i. Management Tools Basic: You will need this to work with SQL server. This isthe GUI interface that includes components such as the GUI Query interface aswell as components for advanced topics such as integration services as well asthe database tuning advisor. -Requiredc. SQL Client Connectivity SDK: Additional Software Development Kit with recourses fordevelopers.d. LocalDB: A lightweight version of the SQL Server Express Database Engine that has thesame programmability features, yet starts on demand and runs in user mode. It is anexecution mode of SQL Server Express targeted to program developers.e. Shared Features Directory: Note the paths where SQL server will install the sharedcomponents (default is Program Files folder within C drive.)21

7. Instance Configurationa. Generally you can leave the Default Instance. The Named instances would be used if youwant to create multiple instances of SQL Server on the same machine. If you areinstalling SQL Server for CS779 this might be a worthy topic to explore for the termproject.b. Choose the installation path for SQL Server (default is Program Files folder within Cdrive.)8. Review the Disk Space Requirements and click next if prompted.22

9. Under Server Configuration set SQL Server Database Engine to Manual (unless you want it torun all the time with your system which will use up a lot of system recourses when you are notusing the Sql Server). These are services that run components of SQL Server.a. You will need SQL Server Database Engine to run SQL Server. Since DBMS uses alot of system recourses, we would recommend running it manually when you need it.b. SQL Server Browser can be left disabled.c. The Account Name for all SQL Server Services should be set to default, NTService\MSSQL SQLEXPRESS. This will allow the services to run with a systemaccount.d. Check the collation tab at the top. For our purposes this can be left at defaultSQL Latin1 General CP1 CI AS which is Latin1-General case insensitive. This driveshow SQL server recognizes commands, for example you can choose a different languageor set it to be case sensitive. Some applications require for you to choose a specificcollation. You can click Customize to change it.23

10. Database Engine Configurationa. Account Provisioning:i. Choose the authentication mode for the system. Windows authentication will useyour windows account privileges to connect to SQL Server. We highlyrecommend using Mixed Mode so that there is an additional built in SAaccount with a separate user name and password as well as your built in windowsaccount.ii. Make sure to add users (such as your account) to SQL ServerAdministrators (click on Add Current User) if it is not already there.iii. These accounts will allow you to log into SQL Server.iv. Note that the server itself does not need these accounts and runs as a servicewhich you specified in previous step.b. You can leave Data Directories to defaults. Data Directories can be changed if you havea multiple disk environment and for performance want to separate out where differentparts of the DBMS go. For example, in production environments the LOG componentsshould go on a separate disk array, which will improve performance of the system.c. If you are installing SQL Server for CS779 you might want to enable FILESTREAM ifyou plan to explore large file types such as Binary language objects (BLOB)11.12.13.14.It is up to you to select Error and Usage Reporting, click next.Click Next after Setup verifies Installation and Configuration Rules.Review selected features and click Install, Installation will begin, this will take some time.Once the installation is complete, if available run a service pack installation. You can check ifthe latest service pack is available from the SQL Server web site.24

Installing Service Packs1. Check the SQL Server web site if the latest service pack is available for your version of SQLServer: spx2. Start the Service Pack Installer after you download it.3. Agree to the License Terms4. Confirm the Installed Features (this is what you installed earlier)5. Check File Use, if some components of SQL Server are running the system will shut themdown/reboot as part of the install. Make sure to wait for the check to complete before clickingNext.6. Ready to Update will confirm what will be updated. – Click on Update to start the process.You are now ready to use SQL Server.25

Working with SQL ServerStarting & Stopping SQL Server (optional)If during setup you selected for SQL Server to start manually then you will need to go to theWindows Control Panel- Administrative Tools- ServicesServices for SQL Server Developer.Start the following service: SQL Server (Instance Name)For CS699, review that SQL Server Analysis Services is using the local account.1. Double click on SQL Server Analysis Services.2. Go to the Log On Tab and choose Local System Account. Click Ok, service willrestart if needed.Note that when you are no longer using SQL Server you can shut the service down to save on systemresources.You can also change the startup type to be automatic while the course is running to save you the stepof turning this on and off.You may want to put the services shortcut to your desktop for quick access26

Logging into SQL Server using SQL Server Management StudioTo work with SQL Server you will use the SQL Server ManagementStudio. You will find it under Microsoft SQL Server 2012 programgroup.You may want to put the SQL Server Management Studio shortcut toyour desktop or pin it to the Windows Task bar for quicker access.In the Connect to Server dialog box:o Server Type: Database Engine (default)o Server Name: This is your system name (default). Tip: If you forget your system name, you can use a single period “.” If you areconnecting to the server instance on the same machine that you are working on.o Authentication: Use Windows Authentication (default) and your accountOR The SQL Server Authentication with Login: SA and password which you createdd

Installing SQL Server 2012 Developer Edition 1. Insert the SQL Server 2012 Developer installation DVD and Run Setup.Exe to start the setup of SQL Server 2012 Developer, if prompted, give administrative permissions. 2. Once the SQL Server Installation Center launches choose Installation tab (s