Skip to main content

1. Starting with SQL Server


Topics In This Module.

  • Responsibilities of Database Administrator 
  • Types of DBAs 
  • History of SQL Server 
  • Editions of SQL Server 
  • Tools of SQL Server 
  • Differences between Standard and Enterprise editions 
  • Instances types in SQL Server 
  • SQL Server Services 

Responsibilities Of A Database Administration:

  1. Managing the databases in an server\instance.
  2. Installing SQL server and Creating, Managing instances.
  3. Patching the SQL server instance, Upgrading and downgrading the SQL server Edition.
  4. Making sure that all the databases in an instance are Backed up successfully if any failed trigger them.
  5. Maintaining the restoring point with clients.
  6. Creating and Managing databases.
  7. Re-Built indexes.
  8. Maintain databases statics timely manner as per requirement.
  9. Maintain the Database health as green and online.
  10. Creating and managing the Maintenance jobs.
  11. SQL Server Monitoring and Working on SQL server alerts if any.
  12. Picking the task in DBA bucket and working on them.
 
Types of DBAs

  There are three types of Database Administrators they are: 

  1. Developer DBA - Develops SQL code, and also maintain SSRS, SSIS and also maintain databases. Simply said L2 and L3 Activities. 
  2. Apps DBA - Create Databases and also in parallelly maintain applications For Example: A share point application and a Share point databases are also maintained by Apps DBA, Simply Said L1 and L2 Activities.
  3. Core MSSQL DBA - We are core SQL DBA's, SQL Server services maintaining, Installing all the required services, not all configuration is needed.


History of SQL Server

  Sybase SQL Server -> SQL Server 6.0 -> SQL Server 6.5 -> 

  <- SQL server 2005 <- SQL Server 2000 <- SQL Server 7.0 <-

-> SQL Server 2008 -> SQL Server 2008R2 -> SQL Server 2012 -> 

 <- SQL Server 2017 <- SQL Server 2016 <- SQL Server 2014 <-

-> SQL Server 2019

 
Editions of SQL Server

  There are different types of edition they are:

  1. Evaluation Edition: Free for 180 days, same as enterprise edition.
  2. Express Edition: Free but there is no Agent services, no jobs  can be created in this edition, 10GB is the Maximum Database size, can allocate only 1Gb RAM.
  3. Developer Edition: Free used For Analysis purposes extended features to test by developers, Only 4 CPUS can be allocated. 
  4. Workgroup Edition.
  5. Web Edition.
  6. Library Edition.
  7. Mobile Edition.
  8. Standard Edition: Licensed, Used by Prod, UAT, Pre-Prod Environments.
  9. Enterprise Edition: Licensed, Used by Prod, UAT, Pre-Prod Environments.

Tools of SQL Server

  The different client tools/components used by SQL Server are:

  1. SSMS - SQL Server Management Studio.
  2. BDC(Big Data Cluster)
  3. SCM (SQL Server Configuration manager).
  4. SSAC(SQL Server Surface Area Configuration)
  5. DTA(Data Tuning Advisor)
  6. Azure Data Studio: Similar to visual studio, for developing.
  7. SQL Profiler: To capture the data.
  8. BCP(Bulk Copy Programming): to copy bulk data from DB to DB we use this BCP utility.
  9. SQLCMD Utility: This is a command line utility, used to connect to database, start the services .. etc
  10. SQL Server Data Tools: These are the tools used for Analysis, Integration, and reporting services. 
SCM (SQL Server Configuration manager): This is introduced from SQL Server 2005, there are many ways to start, stop, manage services one of the way is using SCM. Using SCM we can change the protocols, change the port.
SSAC(SQL Server Surface Area Configuration): This is deleted from SQL Server 2005, Usually used to enable by default features which are disable like DB mail
SQL Server Data Tools:
SQL Profiler:

The are different Protocols they are:
    Shared Memory: We use this when server and client are on the same machine
    Named pipes: We use this when server and client are on same LAN 
    TCP/IP: We Use this when server and client are on different networks, uses WAN.
    VIA: Virtual interface Adapter.
    
In SQL Server there are different port types they are:
    Static port: will be same always, default is 1433
    Dynamic: Will be changing when ever services are restarted.


Differences between Standard and Enterprise editions

  All features present in standard edition are present in Enterprise edition also, some of the other features in enterprise are:

  1. High Availability features (from 2016 we can configure HA in standard as well).
  2. More RAM, and CPU can be allocated to SQL Server in this.
  3. Online Index Re-built is here.
  4. Resource Governor is here.
  5. Resources like 128GB RAM can be allocated and 24 Cores of CPU's are allocated.
  6. Multi Node failover cluster.

If any DML operations are performed data will go into database in an unorganized way which leads to fragmentation, as fragmentation is more any select/search operation takes more time then usual, which is an performance issue.

So to decrease the fragmentation level we use index rebuild.

***********************************************************************

Instances types in SQL Server

  There are two types instance they are:

  1. Default Instance: These type of instances can be only 1 per server as the instance name is itself the server name so only 1 default instance can be crated per server.
  2. Named Instance: These type of instance can maximum 50 instances per servers.
A server can contain only 50 Instances - in which only 1 instance can be the default instance, rest all can be named instance, in other case we can keep all 50 instances as named instances in a server. 

  • For SQL Server 7.0 we can have 1 instance per sever (No Named instances)
  • For SQL Server 2000 we can have 16 Instances per server
  • For SQL Server 2008 We can have 50 Instance per server 

SQL Server Services

There are different services provided by MS-SQL server, These different SQL Server services are also called as SQL Server components they are

  1. SQL server Engine Services.
  2. SQL server Agent.
  3. SQL server full text demon launcher.
  4. SQL server browser.
  5. SQL server Integration Services
  6. SQL server Reporting Services.
  7. SQL server Analysis Services.
  8. SQL server ADH: Integration of SQL with AD on SQL server 2000(Not shown in configuration manager).
  9. SQL server writer: Added functionality to backup and restore VSS (Not Shown in configuration manager).
Types of SQL Server Services
There are two types SQL Services they are:
  1. Instance Aware services.
  2. Instance Unaware services
Instance Aware Services: These are specific services to an instance 1 per instance, below services are the instance aware services.
    • SQL server Engine Services
    • SQL server Agent.
    • SQL server full text demon launcher.
    • SQL server Reporting Services.
    • SQL server Analysis Services.

Instance Unaware Services: These are shared services across servers have mostly 1 per server, below services are the Unaware services.
  • SQL server Browser.
  • SQL server Integration Services.
  • SQL server ADH.
  • SQL server writer.  












Labels:

Comments

Post a Comment

Popular posts from this blog

6. SQL Server Agent.

All SQL Server Agent details are maintained by MSDB databases, which store all the below information All individual jobs and maintenances plan related jobs. Schedules of all jobs. Operator, Database mails. All Alerts related information. All the Error log information is stored here. What is SQL server Agent? SQL Server is an individual service and it’s a default service which comes with the SQL server engine service, All the agent is stored in msdb. Why SQL server Agent? This SQL server agent is used for providing automation for all SQL server administrative tasks. What is operator? All the notifications of a job are received by this person based on the job status. What is Database mail? Database mail is the from address from where and which SMTP the notification to Operator need to send is given while configuring database mail, Only user with DatabaseMailUser role can configure this, By default Database mail is not enabled in older versions, we use SSAC(SQL Surface Area Conf...
Post a Comment
Read more

Index

Microsoft SQL Server Database  Administration  Concepts. Module1:  Starting with SQL Server   Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  Default Instance  Named Instance  SQL Server Services  Instance aware services  Instance unaware services Module2:  Installation, Patching, Migration, Upgradation of SQL Server.   Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. Module3:  Creation and Managing SQL Server Databases Databases. Types of databases and Its Properties Database Architecture Page Extent Files File Groups Modifying Database Modifying Files and File Parameters File Movement Database Corruption Log File ...
Post a Comment
Read more

Microsoft SQL Server Architecture

MS-SQL Server is a Client-Server Architecture. MS-SQL Server process starts with the client application sending a request, The SQL Server accepts, process and replies to the request. SQL server Architecture contains the following major components they are Protocol Layer. Relational Engine. Storage Engine. Databases (Files). Protocol Layer This supports three type of client-server architecture            1.   Shared Memory :  SHARED MEMORY PROTOCOL  Here MS-SQL server provides and supports shared memory protocol here CLIENT and MS-SQL server are on the same machine. To get connected to the default instance on the machine we can use SSMS (SQL Server Management Studio) and to connect use any of the type in SSMS connection method ".", "localhost", "127.0.0.1", "Machine_name\Instance_name"           2.  TCP/IP :  The client and MS-SQL server is on different physical location and different machines, whic...
Post a Comment
Read more

2. Installation, Patching, Migration, Upgradation of SQL Server.

Topics In This Module. Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. *** ============================================================================*** Environments There are two types of environments in general they are: Pre-Prod Environments. => Which Involves Dev environment and UAT environment. Prod Environments. => We do have OLAP (Online Analytical Processing) and OLTP (Online Transactional Processing) Servers in production. SQL Server ->  Instance  ->  database  ->  File groups  ->  Files  ->  as two types below The Files in SQL Server are of two types they are Data files  ->  Extends  ->  Pages  ->  Header part, Data part(8060 Bytes), Row off Set Log files  ->  Virtual logs  ->  LSN  ->           ...
Post a Comment
Read more