Difference between revisions of "MS SQL"

Jump to navigation Jump to search

MS SQL (view source)

Revision as of 13:42, 22 September 2019

16,233 bytes added ,  13:42, 22 September 2019
m
→‎Commands and Maintenance: Added "Show database recovery modes"
m (Added Applications category)
m (→‎Commands and Maintenance: Added "Show database recovery modes")
 
(21 intermediate revisions by the same user not shown)
Line 1: Line 1:
== 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 [[:Category:vCentre|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..
* http://lonesysadmin.net/2011/10/06/how-to-install-microsoft-sql-server-2008-r2-for-vmware-vcenter-5/
If installing in a [[Acronyms#V|VM]], perform a snapshot 1st in case you make a mistake and want to reattempt from scratch.
# Create a local user account for MS SQL<ref name="SQL-User" />, eg SQL-DB, with a nice long password with only the following options checked
#* ''User not allowed to change password''
#* ''Password never expires''
# Run the setup.exe from the root of the ISO (or allow to autorun)
# The installer may need to install .NET and upgrade Windows Installer
## Click '''OK''' if so to allow
## Accept the .NET Framework EULA
## On completion on .NET Framework install, click '''Exit''' to proceed
# Click on '''Installation''' to bring up the install options
# Click on '''New SQL Server stand-alone...''' to launch the install wizard
# Assuming the ''Setup Support Rules'' check succeeds, click '''OK''' to proceed
#* Fix any issues found in order to be able to proceed further
# If required, enter a valid product key, then click '''Next''' to proceed
# Accept the EULA, then click '''Next''' to proceed
# Click '''Install''' in order to install the Setup Support Files
# 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
# 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''
# On the Instance Configuration page, change the Instance root directory if you want to store the database somewhere else, click '''Next''' to proceed
# Click '''Next''' past the ''Disk Space Requirements'' page
# On the ''Server Configuration'' page, change the following then click '''Next''' to proceed
## Select ''Use the same account for all SQL Server services'' and enter the local account created earlier (eg for SQL-DB)
## Change ''SQL Server Agent'' service startup type to ''Automatic''
# 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)
# Click '''Next''' to proceed past the next few pages, and then '''Install''
# Install should complete successfully, then click '''Next''', and then '''Close''' to complete.
# Install the latest SQL service pack, then run Windows Update manually to ensure the install is fully patched
# Even if not asked to, reboot the machine, then log back in and ensure all SQL services have started correctly
# Limit memory available to SQL (optional)
## Start ''SQL Server Management Studio'' and login as one of the SQL administrators you added earlier
## Right-click over the SQL server and select '''Properties'''
## Go to ''Memory'' and set ''Maximum server memory (in MB)'' to something sensible <ref name="SQL-Mem" />
##* 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
# Allow ''Local Launch'' (optional, but said to fix some scheduling and spurious event log error issues)
## Go into ''Component Services'' (Start | Administrative Tools | Component Services)
## Find, right-click and select ''Properties'' over ''Component Services'' | ''Computers'' | ''My Computer'' | ''DCOM Config'' | ''MsDtsServer100''
## Go to the ''Security'' tab and within the ''Launch and Activation Permission'' section, click '''Edit'''
## '''Add''' the local SQL user (eg SQL-DB), and ensure ''Local Launch'' is checked
## Click '''OK''' as required to apply and exit
Additional notes...
<references>
<ref name="SQL-User">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 [[:Category:vCentre|vCentre]]) which should be able to run as stand-alone services without dependence on services such as [[Acronyms#D|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].</ref>
<ref name="SQL-Mem">If installing SQL on the same machine as [[:Category:vCentre|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.</ref>
</references>
== Standard Maintenance Plans ==
== Standard Maintenance Plans ==
=== Daily Maintenance Plan ===
=== Daily Maintenance Plan ===
Line 4: Line 67:
#* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
#* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
#* Older than 2 weeks
#* Older than 2 weeks
# '''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
# '''Reorganise Index Task'''
# '''Reorganise Index Task'''
#* All user databases
#* All user databases
Line 11: Line 79:
#* Update: All existing statistics
#* Update: All existing statistics
#* Scan type: Full scan
#* Scan type: Full scan
# '''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
# '''Maintenance Cleanup Task'''
# '''Maintenance Cleanup Task'''
#* Delete files of the following type: Backup files
#* Delete files of the following type: Backup files
#* File extension: <code>bak</code>
#* File extension: <code>bak</code>
#* File age:  Select as appropriate
#* 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 ===
=== Hourly Transaction Log Backup ===
Line 30: Line 95:
#* File age:  Select as appropriate
#* File age:  Select as appropriate


== Commands ==
== Gain Sysadmin Rights ==
Crib sheet of some very basic stuff
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...!
# Login to the server as a local admin (any account with local admin rights)
# 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
# Ensure that any ''Microsoft SQL Server Management Studio'' instances are closed
# Start the SQL instance in single-user mode
## Open a command prompt in the <code>Binn</code> folder of the instance
##* EG <code> C:\Program Files\Microsoft SQL Server\MSSQL10.DB\MSSQL\Binn </code>
## If running as a named instance noted above use (replace <code><Instance></code>)...
##* <code>sqlservr.exe -s<Instance> -m -c</code>
## Otherwise use...
##* <code>sqlservr.exe -s<Instance> -m -c</code>
## In amongst other messages you should see the following to state that the DB is up and ready
##* <code>SQL Server is now ready for client connections. This is an informational message; no user action is required.</code>
# Connect to the SQL instance (see [[#Unable_to_Connect_in_Single_User_Mode|below]] for probs)
## Open another command prompt in the <code>Binn</code> folder of the instance
## If running as a named instance noted above use (replace <code><ComputerName></code> and <code><Instance></code>)...
##* <code>sqlcmd -S<ComputerName>\<Instance></code>
## Otherwise use (replace <code><ComputerName></code>)...
##* <code>sqlcmd -S<ComputerName></code>
# Add the required accounts into the sysadmin role (replace <code><Login></code> with Windows account
## <code> sp_addsrvrolemember '<Login>', 'sysadmin'</code>
##* EG <code> sp_addsrvrolemember 'DOMAIN\user', 'sysadmin'</code>
## <code>GO</code>
## Repeat the above two steps as required
# 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 <code>Ssms.exe</code> or <code>Ssms.exe *32</code> 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]]


<source lang="sql">
<source lang="sql">
SELECT * FROM MASTER..SYSDATABASE       /* Show databases */
SELECT * FROM MASTER..SYSDATABASES       /* Show databases */
EXEC sp_databases;                                                            /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'       /* Show tables */
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) */
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 */
</source>
=== Manual Backup ===
<source lang="sql">
BACKUP DATABASE DatabaseName TO DISK='E:\bak\DatabaseName.bak';                /* Full database backup */
BACKUP LOG DatabaseName TO DISK='E:\bak\DatabaseName.trn';                    /* Transaction log backup */
</source>
=== Database Sizes ===
Use the following in order to find the sizes of databases on your server.
<source lang="sql">
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>
Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes
=== Table Sizes ===
Replace '''DatabaseName''' in the query below
<source lang="sql">
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>
Source: https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database
=== Memory Usage ===
<source lang="sql">
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;
</source>
=== Fragmentation ===
List index fragmentation within a particular database...
<source lang="sql">
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;
</source>
Indexes identified as being fragmented can defrag'ed by using either of the following command...
<source lang="sql">
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 */
</source>
Alternatively, if you've got lots of indexes to defrag, you can create the REORGANIZE statements...
<source lang="sql">
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;
</source>
...or the REBUILD statements...
<source lang="sql">
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;
</source>
==== Rebuild All Indexes ====
Unless you are running SQL Enterprise, this needs to be run when your database is '''single-user mode'''.
<source lang="sql">
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
</source>
To put into Single-User Mode
# Take database offline
#* If database won't stop, look for processes still accessing the database and kill them
# Put into single-user mode
# Put database online
#* You can only run commands from one console window/connection
<source lang="sql">
/* 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
</source>
</source>


[[Category:Applications]]
[[Category:Applications]]
[[Category:Microsoft]]
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/2014...2714’

Navigation menu