MS SQL: Difference between revisions

From vwiki
Jump to navigation Jump to search
m (→‎Commands: Minor update)
(Re-ordered sections)
Line 92: Line 92:
#* File extension: <code>trn</code>
#* File extension: <code>trn</code>
#* File age:  Select as appropriate
#* File age:  Select as appropriate
== Commands ==
Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]]
<source lang="sql">
SELECT * FROM MASTER..SYSDATABASES       /* Show databases */
EXEC sp_databases;                                                            /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'       /* Show tables */
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */
</source>


== Gain Sysadmin Rights ==
== Gain Sysadmin Rights ==
Line 144: Line 134:
* No SQL services are running
* No SQL services are running
* No ''Microsoft SQL Server Management Studio'' instances are running (including via RDP, check for <code>Ssms.exe</code> or <code>Ssms.exe *32</code> in ''Task Manager''
* No ''Microsoft SQL Server Management Studio'' instances are running (including via RDP, check for <code>Ssms.exe</code> or <code>Ssms.exe *32</code> in ''Task Manager''
== Commands and Maintenance ==
Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]]
<source lang="sql">
SELECT * FROM MASTER..SYSDATABASES       /* Show databases */
EXEC sp_databases;                                                            /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'       /* Show tables */
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */
</source>


[[Category:Applications]]
[[Category:Applications]]
[[Category:Microsoft]]
[[Category:Microsoft]]

Revision as of 09:20, 19 September 2013

Installation

SQL 2008 Standard

Instructions below made from MS SQL 2008 Standard install ISO. These notes were created when installing a SQL server for use with VMware vCentre, therefore may contain some specifics for that usage, see the following if you're installing for vSphere 5, it has much greater detail..

If installing in a VM, perform a snapshot 1st in case you make a mistake and want to reattempt from scratch.

  1. Create a local user account for MS SQL[1], eg SQL-DB, with a nice long password with only the following options checked
    • User not allowed to change password
    • Password never expires
  2. Run the setup.exe from the root of the ISO (or allow to autorun)
  3. The installer may need to install .NET and upgrade Windows Installer
    1. Click OK if so to allow
    2. Accept the .NET Framework EULA
    3. On completion on .NET Framework install, click Exit to proceed
  4. Click on Installation to bring up the install options
  5. Click on New SQL Server stand-alone... to launch the install wizard
  6. Assuming the Setup Support Rules check succeeds, click OK to proceed
    • Fix any issues found in order to be able to proceed further
  7. If required, enter a valid product key, then click Next to proceed
  8. Accept the EULA, then click Next to proceed
  9. Click Install in order to install the Setup Support Files
  10. Assuming the Setup Support Rules check succeeds, click Next to proceed
    • Fix any issues found in order to be able to proceed further, warnings don't prevent installation, but should be read to understand why they have occurred
  11. On the Feature Selection page, select the following features, and change the install paths if required, then click Next to continue
    • Instance Features | Database Engine Services
    • Shared Features | Client Tools Connectivity
    • Shared Features | Integration Services
    • Shared Features | Management Tools – Basic
    • Shared Features | Management Tools – Complete
  12. On the Instance Configuration page, change the Instance root directory if you want to store the database somewhere else, click Next to proceed
  13. Click Next past the Disk Space Requirements page
  14. On the Server Configuration page, change the following then click Next to proceed
    1. Select Use the same account for all SQL Server services and enter the local account created earlier (eg for SQL-DB)
    2. Change SQL Server Agent service startup type to Automatic
  15. On the Database Engine Configuration page, change the following then click Next to proceed
    • Change to Mixed Mode, and enter a nice long password for the SQL root account
    • Add SQL administrators (at minimum I'd suggest the machine's local admin and yourself)
  16. Click Next' to proceed past the next few pages, and then Install
  17. Install should complete successfully, then click Next, and then Close to complete.
  18. Install the latest SQL service pack, then run Windows Update manually to ensure the install is fully patched
  19. Even if not asked to, reboot the machine, then log back in and ensure all SQL services have started correctly
  20. Limit memory available to SQL (optional)
    1. Start SQL Server Management Studio and login as one of the SQL administrators you added earlier
    2. Right-click over the SQL server and select Properties
    3. Go to Memory and set Maximum server memory (in MB) to something sensible [2]
      • SQL will grab as much physical RAM as possible in order to cache and improve performance, therefore you need to limit what it can grab so that the OS, and any other applications will have enough space to run without paging
  21. Allow Local Launch (optional, but said to fix some scheduling and spurious event log error issues)
    1. Go into Component Services (Start | Administrative Tools | Component Services)
    2. Find, right-click and select Properties over Component Services | Computers | My Computer | DCOM Config | MsDtsServer100
    3. Go to the Security tab and within the Launch and Activation Permission section, click Edit
    4. Add the local SQL user (eg SQL-DB), and ensure Local Launch is checked
    5. Click OK as required to apply and exit

Additional notes...

  1. ↑ If you're creating a SQL server for a front-end service, then its usual to use domain service accounts to run SQL under. However, if you're creating a SQL server back-end infrastructure purposes (such as vCentre) which should be able to run as stand-alone services without dependence on services such as DNS or Active Directory servers then you must use a local account. Back-end core infrastructure should be as isolated as possible from other systems, so that when failures or disasters occur, impact is localised and you don't end up with the chicken and egg or deadlock situation where you can't bring one service up, because it depends on another, which can't be brought up because...[etc].
  2. ↑ If installing SQL on the same machine as vCentre, give SQL 25-50% of available RAM. For example, I'd use around 300 MB for a small lab/test environment of 1 or 2 ESX's, whereas if managing 40 ESX's with 1000 VM's you'd need around 2 GB.

Standard Maintenance Plans

Daily Maintenance Plan

  1. History Cleanup Task
    • Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
    • Older than 2 weeks
  2. Reorganise Index Task
    • All user databases
    • Compact large objects
  3. Update Statistics Task
    • All user databases
    • Update: All existing statistics
    • Scan type: Full scan
  4. Back Up Database Task
    • Backup type: Full
    • Databases: All databases
    • Backup will expire: Leave unticked
    • Create a backup file for every database: Update as appropriate
  5. Maintenance Cleanup Task
    • Delete files of the following type: Backup files
    • File extension: bak
    • File age: Select as appropriate

Hourly Transaction Log Backup

  1. Back Up Database Task
    • Backup type: Transaction Log
    • Databases: All user databases
  2. Maintenance Cleanup Task
    • Delete files of the following type: Backup files
    • File extension: trn
    • File age: Select as appropriate

Gain Sysadmin Rights

In order to gain sysadmin control over a SQL instance you need to have local administrator access over the server, and be able to shut the SQL service down. You should only need to do this if nobody has sysadmin rights, and trying to add gain sysadmin rights or perform other tasks fails with errors similar to...

  • User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

If your running in a virtual machine, take a snapshot 1st for piece of mind...!

  1. Login to the server as a local admin (any account with local admin rights)
  2. Stop the SQL Server service and any other SQL services
    • If the database is running as a named instance the name will appear in brackets in the service name - note the instance name
  3. Ensure that any Microsoft SQL Server Management Studio instances are closed
  4. Start the SQL instance in single-user mode
    1. Open a command prompt in the Binn folder of the instance
      • EG C:\Program Files\Microsoft SQL Server\MSSQL10.DB\MSSQL\Binn
    2. If running as a named instance noted above use (replace <Instance>)...
      • sqlservr.exe -s<Instance> -m -c
    3. Otherwise use...
      • sqlservr.exe -s<Instance> -m -c
    4. In amongst other messages you should see the following to state that the DB is up and ready
      • SQL Server is now ready for client connections. This is an informational message; no user action is required.
  5. Connect to the SQL instance (see below for probs)
    1. Open another command prompt in the Binn folder of the instance
    2. If running as a named instance noted above use (replace <ComputerName> and <Instance>)...
      • sqlcmd -S<ComputerName>\<Instance>
    3. Otherwise use (replace <ComputerName>)...
      • sqlcmd -S<ComputerName>
  6. Add the required accounts into the sysadmin role (replace <Login> with Windows account
    1. sp_addsrvrolemember '<Login>', 'sysadmin'
      • EG sp_addsrvrolemember 'DOMAIN\user', 'sysadmin'
    2. GO
    3. Repeat the above two steps as required
  7. Restart the server

Source - http://support.microsoft.com/kb/937682

Unable to Connect in Single User Mode

When trying to connect to a SQL server instance in single user mode you received an error similar to...

  • Login failed for user 'DOMAIN\user'. Reason: Server is in single user mode. Only one administrator can connect at this time.

This is caused by another user (or piece of software) having already looged into the SQL instance. Ensure that...

  • Any applications that use the SQL instance are not trying to connect (disconnect the SQL server from the network if required)
  • No SQL services are running
  • No Microsoft SQL Server Management Studio instances are running (including via RDP, check for Ssms.exe or Ssms.exe *32 in Task Manager

Commands and Maintenance

Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also MySQL 

SELECT * FROM MASTER..SYSDATABASES					       /* Show databases */
EXEC sp_databases;                                                             /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'	       /* Show tables */
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */
Retrieved from ‘http://vwiki.co.uk/index.php?title=MS_SQL&oldid=2387’