Downloaded 12 times
Uploaded byRahul Singh
Microsoft sql server database administration
This document provides an overview of Microsoft SQL Server, including its installation, administration, and various high-availability solutions. It discusses key components such as system databases, indexing for performance, backup types, and automation of daily tasks through SQL Server Agent. Additionally, it covers monitoring tools and new features in SQL Server, enhancing performance and usability.
Microsoft sql server database administration
- 1. B Y RA H U L S I N G H MICROSOFT SQL SERVER ADMINISTRATION
- 2. MICROSOFT SQL SERVER •Introduction • Installation • Administering Microsoft SQL Server • Troubleshooting and Tuning
- 3. MICROSOFT SQL SERVER INTRODUCTION •MSSQL server is a database software provided by Microsoft organization • It handles large databases having millions of records efficient and effectively • Data integrity , availability , easy to use is the key aspect of the product
- 4. INSTALLATION • Considering Editions: Different editions are available to fulfill variety of needs (Enterprise , Standard , Business Intelligence) • Determining hardware requirements (cpu,memory,disk) • Consolidation with Virtual Servers • Collation – How data would be sorted consideration on case sensitivity
- 5. CHOOSING A HIGH-AVAILABILITY SOLUTION •HA means if you have database and if it goes down then another server would become active and provide service • Various methods are available for HA solutions in Microsoft sql server namely • Failover clustering • Database Mirroring • Log- Shipping • Replication • Alwayson
- 6. INSTALLING AND UPGRADING •Choose the features you want to install namely Database Engine, Replication , BI , Integration Services • Configuring the Instance (Sp_configure ) • Command-Line Installation vs GUI • Microsoft SQL Server Upgrade Advisor for upgrading the server to higher versions
- 7. POST INSTALLATIONS • Afterpost installations there are variety of options which you need to configure • Service account through which sql service would run • Sql Server Network configuration (TCP protocol) • Setting Minimum and maximum memory allocated to the sql server • Enabling Backup Compression
- 8. ADMINISTERING MICROSOFT SQL SERVER •Policy based management – policy can be created for example if backup is failed a mail alert would be fire through the system • Central Management server – tool is used to configure multiple servers for example if you need to create a table on 1000+ server you can create it using a single command
- 9. MANAGING SECURITY • Windowslogin can be added for access • Sql server login can be created for access • Schema is a logical container in which tables are created • Many different types of roles and permissions can be added in the DB instance • Data can be encrypted
- 10. SYSTEM DATABASES • Sqlserver has 4 basic types on in built system database • Master – used for storing configuration information • Msdb - used for storing scheduling information • Tempdb- used for temporary operations • Model - A template for newly db created
- 11. DIFFERENT TYPES OFOBJECTS IN DB • Tables --- Has actual data • Views --- Virtual table pointing to tables • Procedures – Programming logics • Functions - Programming logics not changing the state of the database • Constraints – Primary key , Foreign key for maintain referential integrity • Data Types – Int, varchar, datetime etc
- 12. INDEXING FOR PERFORMANCE •Index can be created on tables to improve the performance of the queries • Varieties of indexes available in sql server namely • Clustered index • Non-clustered index • Full text indexes • Spatial indexes • Filtered indexes • Covering indexes
- 13. INDEX MAINTENANCE • Overa period of time index performance gets degraded • Fill factor (Page fill option) should be set to 80% • Index should be rebuild or reorganize to remove any kind of internal as well as external fragmentation
- 14. BACKUPS OF DBS •Different types of backups are available in sql server to product namely • Full Backup which takes entire db backup • Differential Backup takes backup for data that has been change since last full backup • Transactional backup takes backup of data since last transactional log backup • First transactional log backup takes data of only transaction that are mark as inactive in transactional log backup of the db
- 15. RESTORE OF DATABASE •Recovery models plays a vital role in recovery strategy • Full , Bulk-logged and simple are available • Full backup can be restore by simple command restore db from disk =‘Path’ with recovery • Differential backup can be restore only by restoring last full backup with norecovery and then restoring differential backup it with recovery
- 16. AUTOMATING DAILY TASKS •Database mail – A mail can be sent from the db using this utilty • Sql server Agent – A scheduler can be configure which will trigger as per schedule specified by you • Various actions can be automated namley Backups , index maintenance , business logics using Sql server agent
- 17. MONITORING SERVER • Performancemonitor – A windows tool can be used to capture cpu, memory , io metrics • DMVS – Dynamic management views and functions - best tool to captures queries causing issues • Activity monitor – GUI tool to monitor Server performance • Sql server Profiler – Capture data on demand different types of events can be captured
- 18. OTHER USEFUL TOOLS •Sql server Audit --- All the activities happening in sql server can be captured using audit tool • Extended Events –New tools introduced similar to profiler but takes very less resources
- 19. NEW FEATURES • OnlineIndex Rebuilds --- Indexes now can be rebuild online which is only available in enterprise edition • Database Restores –Gui option given for page level restores • Contained Databases – New concept making database more portable to move across servers
- 20. PLEASE LIKE ANDSUBSCRIBE/FOLLOW Thank you