MS SQL

From vwiki
Jump to navigation Jump to search

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. 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
  3. Reorganise Index Task
    • All user databases
    • Compact large objects
  4. Update Statistics Task
    • All user databases
    • Update: All existing statistics
    • Scan type: Full scan
  5. Maintenance Cleanup Task
    • Delete files of the following type: Backup files
    • File extension: bak
    • File age: Select as appropriate

Be aware rhat the Reorganise Index Task can cause lots of activity to be generated in the transaction log, therefore if your recovery mode is Full (rather than Simple), the transaction log can grow large very quickly. If performing on a database that's been running for some time this can be an issue. Consider setting the recovery mode to Simple for the first run, or perform at a time when if your transaction log fills you can recover without too much disruption to applications using the database.

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

For performance issues, see http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

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) */
EXECUTE sp_who2;                                                               /* Show current processes */
SELECT name,log_reuse_wait_desc FROM sys.databases;                            /* Show why transaction log can't grow */
SELECT name,recovery_model_desc FROM sys.databases ORDER BY name               /* Show database recovery modes */

Manual Backup

BACKUP DATABASE DatabaseName TO DISK='E:\bak\DatabaseName.bak';                /* Full database backup */
BACKUP LOG DatabaseName TO DISK='E:\bak\DatabaseName.trn';                     /* Transaction log backup */

Database Sizes

Use the following in order to find the sizes of databases on your server. 

SELECT
    DB_NAME(db.database_id) DatabaseName,
    ((CAST(mfrows.RowSize AS FLOAT)*8) + (CAST(mflog.LogSize AS FLOAT)*8))/1024 TotalSizeMB,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes

Table Sizes

Replace DatabaseName in the query below 

USE <DataBaseName>
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id)

Source: https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database

Memory Usage

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

Fragmentation

List index fragmentation within a particular database... 

USE database_name;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 50 
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Indexes identified as being fragmented can defrag'ed by using either of the following command... 

ALTER INDEX IndexName ON TableName REORGANIZE;                       /* Online */
ALTER INDEX IndexName ON TableName REBUILD;                          /* Offline */
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON);       /* Online - 2008 Ent and above only */

Alternatively, if you've got lots of indexes to defrag, you can create the REORGANIZE statements... 

SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 50;

...or the REBUILD statements... 

SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD WITH (ONLINE = ON);' 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 50;

Rebuild All Indexes

Unless you are running SQL Enterprise, this needs to be run when your database is single-user mode. 

USE DatabaseName                            /* Change to database name! */
 
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 
 
OPEN TableCursor 
  
FETCH NEXT FROM TableCursor INTO @TableName 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
DBCC DBREINDEX(@TableName,' ',0) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
 
CLOSE TableCursor 
 
DEALLOCATE TableCursor

To put into Single-User Mode

  1. Take database offline
    • If database won't stop, look for processes still accessing the database and kill them
  2. Put into single-user mode
  3. Put database online
    • You can only run commands from one console window/connection
/* Find processes and kill them*/
exec sp_who2
kill 32

/* Force offline - change database name */
USE master
ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

/* Force into single-user */
USE master
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/* Put back into multi-user */
USE master
ALTER DATABASE DatabaseName SET MULTI_USER
Retrieved from ‘http://vwiki.co.uk/index.php?title=MS_SQL&oldid=2714’