SQL Server DBA Notes:
SSL Certificate: Secure Sockets Layer (SSL) encryption:
The Secure Sockets Layer (SSL) can be used to encrypt data transferred on network between our SQL Server instance and a client application.
Coming to installation:
- Need to go to MM console (Microsoft management: run --> MMC )
- On the File menu, click Add/Remove Snap-in...
- Select Certificates, click Add.
- computer account --> local computer --> finish.
--Configuring SQL Server to accept encrypted connections:
- Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, then click Properties.
- On the Certificate tab, select the certificate you would like to use.
- On the Flags tab, select Yes in the ForceEncryption box, then click OK.
- Restart the SQL Server service.
SQL Server 2012:
Editions:
-------------
Enterprise edition
Satndard edition
Devoloper edition
Web edition
Express editon
-- CAPACITY PLANNING:
Whenever we need to do capacity planing, we need to consider few things like
- Backup space : how much backup space abilbale in the disks
- Snapshot space: If the DB in TBs at that time it will be useful to mitigate the time
- How much Archive space avilable
- ReIndex (Including multiple db's reindexing at the same time)
- Checkdb
- How much TEMPDB action: how much it will grow need to consider
- Enough spindles to perform well
----------------------------------
INSTALLATION ON SQL SERVER 2012 WITH POWER SHELL - GO TO ADMIN TOOLS - POWERSHELL
-- GET ALL THE COMPONETS AVILABLE IN THE SERVER:
GET-WINDOWSFETURE
----------------
-- STATISTICS:
-----------------
sTATISTICS are used by SQL Server to decide which index to use when you perform a query.
- Statistics are needs to maintain to be effective.
Statistics update Modes:
- Synchronous forces queries to wait when stats are out of date
- Asynchronous allows queries to continue with old stats and builds new ones in the background.
Query to check the options are enable in the db:
--SELECT NAME, IS_AUTO-UPDATE-STATS_ON, IS_AUTO_UPDATE_STATS_ASYNC_ON
FROM SYS.DATABASES
===============
Resticted Access:
- Single User: Allows only one User to access the DB
- Resticted User: Allows only db_owner role, sysadmin, or dbcreator
- Multi User: Allows all users
DMV's:
------
sys.dm_exec_sessions - Sessions in SQL Server
sys.dm_exec_connections - Connections to SQL Server
sys.dm_db_index_usage_stats - Seeks, scans, lookups per index
sys.dm_io_virtual_file_stats - IO statistics for databases and log files
sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server
sys.dm_exec_query_plan - Returns query plan
sys.dm_os_wait_stats - Returns information what resources SQL is waiting on
sys.dm_os_performance_counters - Returns performance monitor counters related to SQL Server
-------------------------------------
DBCC: DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.
- DBCC CHECKDB :
CHECKS ALLOCATION AND CONSISTENCY errors
--DBCC CHECKCATALOG
Used to perform consistency check for a table/catalog for a particular database.
--DBCC CHECKALLOC
Used to check page usage and allocation of a database.
--DBCC CHECKTABLE(tablename)
Used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.
- DBCC SQLPERF (LOGPSACE) -
Displays T-log size and space used % for all databases
- DBCC INPUTBUFFER(sessionid)
Used to get the last statement sent by session to SQL server.
-DBCC HELP(<dbcc command>)
Displays the syntax of a DBCC command. DBCC HELP ('?')
To know syntax of DBCC SQLPERF, execute DBCC HELP ('SQLPERF')
- DBCC SHRINKDATABASE ('DBNAME', 10): DONT USE BECAUSE OF FAGMENTAION HAPPEND IN THE Db
Used to shrink data and log files for a particular database.
It release the free space to OS
- DBCC SHRINKFILE ('DBNAME', 5) : Used to shrink individual database files.
- DBCC LOGINFO
Displays virtual log file information contained in a T-log file.
- DBCC SHOWFILESTATS
Display Extent information for a database.
- DBCC TRACEON/TRACEOFF: USED TO ENABLE TRACING/ DISABLE TRACING.
- DBCC TRACESTATUS
Displays the trace status.
- DBCC USEROPTIONS
Displays the active SET options for a particular database
- DBCC MEMORYSTATUS
Displays SQL Server memory allocation status.
- DBCC Opentran
Displays information about oldest active transaction and oldest distributed and non-distributed replication transaction
CLUSTER: A Group of Servers can act as a sigle server.
----------------------
CLUSTER NODE : A Cluster node is a server with in the cluster and it has windows server and the cluster service installed.
CLUSTER Service: The cluster service manages all the activities that is specific to the cluster.
One instance of cluster service runs on each node in the cluster.
the Cluster service does the activities like
- Manages Cluster objects and configurations
- Manages the local restart policy
- Perform failover operations
cluster Group: A Cluster Group is a collection of logically grouped cluster resorces. It may contain cluster-aware application services
like SQL Server Group, file server etc.
Private network is used for cluster communication(LooksAlive and IsAlive checks) and Public network is used for client applications to connect.
When the private network goes down cluster communications will carry on through the public network. Loss of a private network does not initiate a failover.
The loss of the public network will initiate a failover since this is the network clients connect through.
Quorum is a count of voting members— a quorum is a way of taking attendance of cluster members who are present.
The cluster uses a quorum to determine who should be online.
When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available.
One is LooksAlive and the other one is IsAlive check.
LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running.
By default this check happens every 5 seconds.
During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and
runs SELECT @@SERVERNAME against the instance.
This check does not check if the user databases are online or not.
It just checks the SQL Server instance availability. This check happens every 60 seconds by default.
AlwaysOn:
---------
- WFC : Windows failover cluster required
- the SQL Servers what ever needs to participate alwaysOn that needs add in WFC
- configuration manager - AlwaysOn feture needs to enable
- DB fulll recovery, Recent full backup needs to complete
Performance counters:
-------------------------
- Memory: avilable in bites counter indicates how many bytes of memory currently avilable for use by processes
High means good heath
low means shortage of memory
- Memory: pages\sec:
high: indicates execive paging
low: good health
- buffer manager: beffer cache hit ratio: high > 90 - good health,
- Total Server Memory is almost same as Target Server Memory - Good Health
Total Server Memory is much smaller than Target Server Memory
– There is a Memory Pressure or Max Server Memory is set to too low.
Page Life Expectancy: the number of seconds the average page of data has been in the buffer pool.
High: Good Health
Low: Memory Pressure or Max Server Memory is not allocated properly
No comments:
Post a Comment