SQL Server 2012
17-November-2014
Revision: Release
This publication is designed to assist in the use of the product as it exists on the date of publication of this manual, and may not reflect the product at the current time or an unknown time in the future. This publication does not in any way warrant description accuracy or guarantee the use for the product to which it refers. Imagine Communications reserves the right, without notice to make such changes in equipment, design, specifications, components, or documentation as progress may warrant to improve the performance of the product.
Trademarks
Product names and other brands (such as ADC™, D-Series™, Nexio®, Nexio® Insight, Nexio® Motion, PowerSmart®, Versio™) are trademarks or trade names of Imagine Communications or its subsidiaries.
Microsoft® and Windows® are registered trademarks of Microsoft Corporation. All other trademarks and trade names are the property of their respective companies.
Contact Information
Imagine Communications has office locations around the world. For domestic and international location and contact information see: http://www.imaginecommunications.com/contact-us/
Support Contact Information
For domestic and international support contact information see:
Support Contacts: http://www.imaginecommunications.com/services/technical-support/
eCustomer Portal: http://support.imaginecommunications.comContents
About this Document ... 5
Summary ... 5
Audience Assumptions ... 5
Installation Prerequisites ... 5
Hardware/Software Requirements ... 5
Database File Server Requirements ... 6
Installing SQL Server 2012 ... 8
Before installing SQL Server 2012 ... 8
Installing SQL Server 2012 ... 8
Install SQL Server 2012 ... 8
Enabling the Named Pipes Communications Protocol ... 17
Create the SQL Server 2012 Database ... 19
Create the Database ... 20
Create the SQL Server 2012 Database Backup ... 22
Create the Database Backup ... 22
Editing the SQL Server 2012 Database Backup Schedule ... 24
Edit the Database Backup Schedule ... 24
About Notification Agent ... 26
Create Notification Agent Database Permissions ... 26
Restoring an SQL Server Database Backup into SQL Server 2012 ... 27
Restoring a Database Backup file into SQL Server 2012 ... 27
Upgrade Database Users and Permissions ... 31
About Notification Agent ... 33
Upgrade Notification Agent Database Permissions ... 33
Additional Considerations ... 34
Configuring ODBC Data Sources using an IP Address ... 42
Configuring ODBC Data Sources using a Computer Name ... 49
Enabling .NET v3.5 ... 57
Enable .NET v3.5 ... 57
About this Document
Summary
This Installation Reference was created when installing Microsoft SQL 2012 Standard on an ADC™ Fileserver running Windows Server 2008 R2 64-bit OS.
Audience Assumptions
This document makes the following audience assumptions:
System Operator level user
Proper user access authorizations are set up.
Experience with Windows Operating systems.
Experience with broadcast operations and systems.
Experience installing Microsoft applications and database applications.
Authorized Training has been provided on this application and the user has successfully completed this training.Installation Prerequisites
The following applications are required for this installation and can be downloaded from the Microsoft web site.
Microsoft Windows Installer 4.5 or later
.NET Framework v3.5
Microsoft SQL Server 2012 Standard EditionHardware/Software Requirements
The following table presents hardware and software requirements for installing SQL Server 2012 on the ADC Database File Server computer.
Imagine Communications automation products must ensure they meet Imagine
Communications recommended minimum hardware specifications. Please contact Imagine Communications support for more information.
Type Requirements
Hardware Processor
- Minimum: 1GHz (x86 processor) or 1.4GHz (x64 processor) - Recommended: 2GHz or faster
- Note: An Intel Itanium 2 processor is required for Windows Server 2008 for Itanium-Based Systems
Memory
- Minimum: 1GB RAM
- Recommended: 4GB RAM or greater
- Maximum (32-bit systems): 4GB (Standard) or 64GB (Enterprise and Datacenter)
- Maximum (64-bit systems): 32GB (Standard) or 1TB (Enterprise, Datacenter) and 2TB (Itanium-Based Systems)
Available Disk Space - Minimum: 6GB
- Recommended: 40GB or greater
- Note: Computers with more than 16GB of RAM will require more disk space for paging, hibernation, and dump files
Drive
- DVD-ROM drive Display and Peripherals
- Super VGA (800 x 600) or higher-resolution monitor - Keyboard
- Microsoft Mouse or compatible pointing device Imagine
Communicatio ns Software
Media Client release 4.16.29.0 or higher Air Client release 4.16.29.0 or higher Operating
System Windows Server 2008 R2 64-bit w/SP1 - Using NetBIOS-Over-TCP/IP - IPv6 (for Notification Agent)
Type Requirements Network
Interface Card Intel NIC (82576 Gigabit Dual Port Network Connection)
An Additional Intel NIC (Pro/1000 PT Desktop Adapter) may be required for systems connecting to an external network.
When communicating via NETBIOS over TCP/IP a Gigabit NIC and Switch is required
Network
Software Stand-alone named and default instances support the following network protocols: Shared memory, TCP/IP, and Named Pipes.
Before installing SQL Server 2012
Windows Server 2008 R2 64-bit should be updated to the most recent Service Pack and updated with the most recent Updates Before installing SQL Server 2012 Standard. These are available through Windows Updates.
For details on the installation of Windows Server 2008 R2 64-bit, refer to “Windows Server 2008 R2 64-bit Install Guide”.
Installing SQL Server 2012
IMPORTANT: SQL Server 2012 will NOT install without having the .NET v3.5 installed in the computer. Ensure .NET v3.5 is installed prior to installing to SQL Server 2012. See Enabling .NET v3.5 for how to do this.
Install SQL Server 2012
1. Load the Microsoft SQL Server 2012 DVD, browse to its directory, and double click on Setup.exe.
2. Wait while Microsoft SQL Server 2012 Setup processes the current operation:
3. When the SQL Server Installation Center opens click on Installation.
4. Click on New SQL Server stand-alone installation or add features to an existing installation.
5. Wait while Microsoft SQL Server 2012 Setup processes the current operation.
6. Verify that all of the Setup Support Rules passed and click OK:
7. Wait while Microsoft SQL Server 2012 Setup processes the current operation.
8. Enter the product key and click Next:
9. Accept the license terms and click Next:
10. The Setup Support Files screen appears and its Status goes to In Progress:
11. Verify that all of the Setup Support Rules passed and click Next:
12. On the Setup Role screen select SQL Server Feature Installation and click Next.
13. On the Feature Selection screen Enable, (check), Database Engine Services, Client Tools Connectivity, Client Tools Backwards Compatibility, Management Tools – Basic, and Management Tools - Complete, and then click Next:
14. On the Installation Rules screen, if desired, view the result of the rules processing. When ready press Next.
15. Verify that Default Instance is selected on the Instance Configuration screen and click Next:
16. Click Next on the Disk Space Requirements screen:
17. On the Service Accounts tab of the Server Configuration screen set the:
• SQL Server Agent to NT Service\SQLSERVERAGENT
• SQL Server Database Engine to NT Service\MSSQLSERVER
• SQL Server Browser to NT AUTHORITY\LOCAL SERVICE
• SQL Server Agent, SQL Server Database Engine, and SQL Server Browser Startup Types to Automatic
18. On the Collation tab of the Server Configuration screen, click Customize in the Database Engine area, select SQL collation, used for backwards compatibility, choose
SQL_Latin1_General_CP1_CS_AS, and click OK:
19. The Collation tab of the Server Configuration screen should now look like this:
20. Click Next on the Server Configuration screen:
21. On the Server Configuration tab of the Database Engine Configuration screen:
• Select Mixed Mode.
• Enter HarrisBroadcastDB1 (case sensitive) in the Enter password area.
• Enter HarrisBroadcastDB1 (case sensitive) in the Confirm password area.
22. On the Data Directories tab of the Database Engine Configuration screen:
• Set the User database directory to D:\Database
• Set the User database log directory to D:\Database
• Set the Backup directory to D:\Backup
Note: Database Servers can have single or multiple partitions. Imagine Communications ships Database Servers with two partitions (\C: and \D:). If required, change the location to
C:\DATABASE, D:\DATABASE, or whatever is needed.
23. Click Next.
24. Verify that the Error Reporting checkbox is not enabled and click Next.
25. Verify that all of the Installation Configuration Rules passed and click Next:
26. Click Install on the Ready to Install screen:
27. Click OK on the Computer restart required message (if it appears):
28. Wait while Microsoft SQL Server 2012 Setup processes the current operation.
29. Click Close on the Complete screen:
30. Restart the computer (if the message in Step 27 appeared).
31. The SQL Server 2012 installation is now complete.
Enabling the Named Pipes
Communications Protocol
The Named Pipes Communications Protocol is not enabled when installing SQL Server 2012. This Communications Protocol must be enabled before client computers can connect to the SQL Server 2012 Database via Named Pipes.
Enable the Named Pipes Communications Protocol
1. Select Start\All Programs\Microsoft SQL Server 2012\Configuration Tools\SQL Configuration Manager:
2. Expand SQL Server Network Configuration, select Protocols for MSSQLSERVER, select Named Pipes, right click, and select Enable:
3. Click OK on the Warning:
4. Verify that Named Pipes is now Enabled:
5. Select SQL Server Services\SQL Server (MSSQLSERVER), right click, and select Restart:
6. This SQL Service will be Stopped:
7. This SQL Service will be Started again:
8. Close the SQL Configuration Manager after this Service has completely re-started.
Create the SQL Server 2012 Database
If this is a new system it is now time to create the SQL Database.
If this is an upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2005 Express, or SQL Server 2008 Express, please go to the section: Restoring an SQL Server Database Backup into SQL Server 2012.
The SQL script CreateDB_All_ADC_SQL_2012_V1_3.sql is provided by Imagine Communications to create an SQL Server ODBC-compliant database. Use the
CreateDB_All_ADC_SQL_2012_V1_3.sql script to create the SQL Database.
Note: Database Servers can have single or multiple partitions. Imagine Communications ships Database Servers with two partitions (\C: and \D:). If required, change the following section (Global variables: @PathName) in the create database script to C:\DATABASE, D:\DATABASE, or whatever is needed.
Create the Database
1. Select Start\Programs\Microsoft SQL Server 2012\SQL Server Management Studio:
2. Click Connect:
Note: Your Server Name might be different than the one shown in the image.
3. Select File\Open File…:
4. Browse to the directory containing the SQL Scripts, highlight CreateDB_All_ADC_SQL_2012_V1_3.sql, and click Open:
5. Click Execute:
6. Verify in the Messages area that the Command (s) completed successfully:
create Backups of all SQL Server Databases. It is set to do the Backups at 9:30 PM and will keep 30 days of them at any given time. The time and frequency of the Database Backups are configurable.
Use the DB_Full_Bkp_SQL_2012_V1_3.sql script to create the SQL Database.
Note: To Change backup destination change code on lines: 69 and 97
Example: Line 69: SELECT @location = ''DriveLetter:\Foldername'' or SELECT @location = ''\\ComputerName\sharename''
Example: Line 97: SET @path = ''DriveLetter:\Foldername\'' or SET @path = ''\\ComputerName\sharename\''
Create the Database Backup
1. Select File\Open File…:
2. Browse to the directory containing the SQL Scripts, highlight DB_Full_Bkp_SQL_2012_V1_3.sql, and click Open:
3. Click Execute:
4. Verify in the Messages area that the Command (s) completed successfully:
5. Close the SQL Server Management Studio.
frequency of the Database Backups. The following steps describe how to edit the Backup Schedule.
Edit the Database Backup Schedule
1. Select Start\Programs\Microsoft SQL Server 2012\SQL Server Management Studio:
2. Expand SQL Server Agent, expand Jobs, highlight BACKUP – Full, right click, and select Properties:
3. Select Schedules, and click Edit:
4. Changes to the time and frequency of the Database Backups can be made in these areas:
5. After any changes, click OK in the Job Schedule Properties dialog, and then click OK in the Job Properties dialog.
6. Close the SQL Server Management Studio.
IMPORTANT: If Notification Agent will be used with this ADC System, then NA should be installed now on the File Server. Notification Agent must be installed prior to Creating
Notification Agent Database Permissions. For instructions on installing Notification Agent, please the Notification Agent Installation and Operations Guide. If additional assistance is required, please contact Automation Support.
Create Notification Agent Database Permissions
The SQL script CreatePermissions_NA_SQL_2012_V1_3.sql is provided by Imagine
Communications to create Notification Agent Permissions for an SQL Server ODBC-compliant database. Use the CreatePermissions_NA_SQL_2012_V1_3.sql script to create NA Permissions.
1. Select File\Open File…:
2. Browse to the directory containing the SQL Scripts, highlight CreatePermissions_NA_SQL_2012_V1_3.sql, and click Open:
3. Click Execute:
4. Verify in the Messages area that the Command (s) completed successfully:
5. Close the SQL Server Management Studio.
Restoring an SQL Server Database Backup
into SQL Server 2012
A backup of the current SQL Server 2005, SQL Server 2008, SQL Server 2005 Express, or SQL Server 2008 Express Database is needed for migration into SQL Server 2012 Standard. See the Creating an SQL Server 2005 Express Database Backup or Creating an SQL Server 2008 Database Backup section if you do not have a current Database backup file.
This section describes how to restore an ASDB.bak Database backup file into SQL Server 2012 Standard.
Note: Make sure that all Client Computers are disconnected from the File Server before performing a Database restore.
Restoring a Database Backup file into SQL Server 2012
1. Create a Folder on the D: Drive named Database.
2. Select Start\Programs\Microsoft SQL Server 2012\SQL Server Management Studio:
3. On the ‘Connect to Server’ dialog, click Connect:
4. Expand the SQL Server, highlight Databases, right click, and select Restore Database…:
5. In the Source area of the Restore Database - window select Device:, and click on the ellipsis, (…):
6. In the Select backup devices window click Add, browse to the location of the ASDB.bak Database backup file, highlight it, and click OK:
7. Click OK in the Select backup devices window:
8. All needed fields are auto-populated:
9. Click on Files, and verify that in the Restore As area that the ASDB_dat and ASDB_log files are configured to the D:\DATABASE directory. If they are not set there by default, manually configure the files to this directory:
11. Click OK in the confirmation window:
Upgrade Database Users and Permissions
The SQL script Upgrade_All_ADC_SQL_2012_V1_2.sql is provided by Imagine Communications to update Users and Permissions for an SQL Server ODBC-compliant database. Use the
Upgrade_All_ADC_SQL_2012_V1_2.sql script to update Users and Permissions, and modify the Database for new functionality. Since users and logins are unique to each SQL server instance, this script must also be used when restoring an ASDB database from another SQL server instance otherwise clients will encounter login errors at startup.
or update existing records in the database.
1. Select File\Open\File…:
2. Browse to the directory containing the SQL Scripts, highlight Upgrade_All_ADC_SQL_2012_V1_2.sql, and click Open:
4. Verify in the Messages area that the Command (s) completed successfully:
About Notification Agent
Note: If Notification Agent was not installed and used with this ADC System then you do not need to perform the operations described in the Upgrade Notification Agent Database Permissions section.
IMPORTANT: If Notification Agent was installed and used with this ADC System then NA should be installed now on the File Server. Notification Agent must be installed prior to Upgrading Notification Agent Database Permissions. For instructions on installing Notification Agent, please reference the Notification Agent Installation and Operations Guide. If additional assistance is required, please contact Automation Support.
Upgrade Notification Agent Database Permissions
The SQL script UpgradePermissions_NA_SQL_2012_V1_2.sql is provided by Imagine
Communications to upgrade Notification Agent Permissions for an SQL Server ODBC-compliant database. Use the UpgradePermissions_NA_SQL_2012_V1_2.sql script to upgrade Notification Agent Permissions.
1. Select File\Open File…:
2. Browse to the directory containing the SQL Scripts, highlight UpgradePermissions_NA_SQL_2012_V1_2.sql, and click Open:
3. Click Execute:
4. Verify in the Messages area that the Command (s) completed successfully:
5. Close the SQL Server Management Studio.
Additional Considerations
When migrating to a new server, the following changes should also be considered:
1. Copy all lists, logs, database backups, scheduled tasks, files and installers to the new server.
2. Copy all desktop/startup files/icon to the new server.
3. Recreate any mapped drives from the old server.
4. Share the lists and database folders.
6. Rather than updating all clients with new server information, set the IP addresses and hostname of the old server to match the old server. This can only be done after the IPs and hostname on the old server are changed or the server is disconnected from the network.
7. After a switch, on the old server:
• Turn off all services and set to manual startup.
• Change the IP addresses and hostname so that it is does not cause confusion or connection problems.
Creating an SQL Server 2005 Express
Database Backup
A backup of the current SQL Server 2005 Express Database must be created for migration into the new SQL Server 2012 Standard Database. This section describes how to create an ASDB.bak Database backup file.
Creating a Database Backup file
1. Select Start\Programs\Microsoft SQL Server 2005\SQL Server Management Studio Express:
2. Enter the Server name and then click Connect:
3. Expand the SQL Server, expand Databases, highlight ASDB, right click, select Tasks, select
4. In the Source area of the Back Up Database - ASDB window verify that ASDB is entered for Database, Full is entered for Backup type, and that Database is selected for the Backup component:
5. In the Backup set area of the Back Up Database - ASDB window verify that ASDB-Full
Database Backup is entered for Name, After is selected for Backup set will expire, and that 0 is entered for After:
6. In the Destination area of the Back Up Database - ASDB window click on Add…, in the Select Backup Destination window click on the ellipsis, (…), browse to and select the Destination directory for the Backup, enter ASDB.bak as the File name, and click OK:
7. Click OK in the Select Backup Destination window:
8. Click OK in the Back Up Database - ASDB window:
9. Click OK in the Backup confirmation window:
10. Close SQL Server Management Studio Express.
Creating an SQL Server 2008 Database
Backup
A backup of the current SQL Server 2008 Database must be created for migration into the new
Creating a Database Backup file
1. Select Start\Programs\Microsoft SQL Server 2008 R2\SQL Server Management Studio:
2. Enter the Server name and then click Connect:
3. Expand the SQL Server, expand Databases, highlight ASDB, right click, select Tasks, select Back Up…:
4. In the Source area of the Back Up Database - ASDB window verify that ASDB is entered for Database, Full is entered for Backup type, and that Database is selected for the Backup component:
5. In the Backup set area of the Back Up Database - ASDB window verify that ASDB-Full Database Backup is entered for Name, After is selected for Backup set will expire, and that 0 is entered for After:
6. In the Destination area of the Back Up Database - ASDB window click on Add…, in the Select Backup Destination window click on the ellipsis, (…), browse to and select the Destination directory for the Backup, enter ASDB.bak as the File name, and click OK:
7. Click OK in the Select Backup Destination window:
8. Select Options, in the Overwrite media area of the Back Up Database window select Overwrite all existing backup sets, and then click OK:
9. Click OK in the Backup confirmation window:
10. Close SQL Server Management Studio.
Configuring ODBC Data Sources
This section describes how to configure automation Client Computers for ODBC connectivity to SQL Server 2012. TCP/IP or Named Pipes ODBC connectivity can be configured using the IP Address or the Computer Name of the File Server. The Windows Firewall must be disabled for Automation ODBC connectivity.
IMPORTANT: When using a 64-bit Operating System, ODBC is started by running ODBCAD32.exe from C:\WINDOWS\SYSWOW64.
Configuring ODBC Data Sources using an IP Address
1. Select Start\Settings\Control Panel\Administrative Tools\Data Sources (ODBC):
3. Scroll down and select SQL Server, and then click Finish:
4. Enter ASDB in the Name field, the File Server IP Address in the Server field, then click Next:
5. Click on Client Configuration:
6. Select TCP/IP, verify that the File Server IP Address is in the Server alias and Server name fields, and then click OK:
7. Or select Named Pipes, verify that the File Server IP Address is in the Server alias and Server name fields, and then click OK:
8. Select “With SQL Server authentication using a login ID and password entered by the user”, enter HarrisDB (this is case sensitive) into the Login ID and Password fields, and then click Next:
IMPORTANT: The Login ID and Password are case sensitive.
9. Check “Change the default database to:”, ensure ASDB is selected, and then click Next:
10. Click Finish:
11. Click Test Data Source:
12. Verify that the Tests Completed Successfully, then click OK:
13. Click OK:
Configuring ODBC Data Sources using a Computer Name
1. Select Start\Settings\Control Panel\Administrative Tools\Data Sources (ODBC):
2. Select the System DSN tab and click Add:
4. On the New Data Source (DSN) Configuration screen, enter ASDB into the Name field, the File Server Computer Name into the Server field, and then click Next:
5. Click on Client Configuration:
6. Select TCP/IP, verify that the File Server Computer Name is in the Server alias and Server name fields, and then click OK:
Or select Named Pipes, verify that the File Server Computer Name is in the Server alias and Server name fields, and then click OK:
7. Select With SQL Server authentication using a login ID and password entered by the user, enter HarrisDB, (case sensitive), into the Login ID and Password fields, and then click Next:
IMPORTANT: The Login ID and Password are case sensitive.
8. Check “Change the default database to:”, ensure ASDB is selected, and then click Next:
9. Click Finish:
10. Click Test Data Source:
11. Verify that the Tests Completed Successfully, and click OK:
12. Click OK:
13. Click OK:
Enabling .NET v3.5
.NET v3.5 must be enabled before installing SQL Server 2012. Follow this procedure to enable .NET v3.5.
Enable .NET v3.5
1. Select Start\Administrative Tools\Server Manager:
2. Click on Features:
3. Click on Add Features in the Features Summary area of the Server Manager:
4. Expand .NET Framework 3.5.1 Features, Enable, (check), .NET Framework 3.5.1, and click Next:
5. Click Install in the Confirm Installation Selections screen:
6. Click Close in the Installation Results screen:
7. Close the Server Manager.
8. Click on this link to return to the Installing SQL Server 2012 section.