SQL Server Question must know by a DBA.

1. What are the Edition of SQL server ?

Enterprise

SQL Server Enterprise Edition includes both the core database engine and add-on services, with a range of tools for creating and managing a SQL Server cluster. It can manage databases as large as 524 petabytes and address 12 terabytes of memory and supports 640 logical processors (cpu cores).

Standard

SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes.

Web

SQL Server Web Edition is a low-TCO option for Web hosting.

Business Intelligence

Introduced in SQL Server 2012 and focusing on Self Service and Corporate Business Intelligence. It includes the Standard Edition capabilities and Business Intelligence tools: Power Pivot, Power View, the BI Semantic Model, Master Data Services, Data Quality Services and x Velocity in-memory analytics.

Workgroup

SQL Server Workgroup Edition includes the core database functionality but does not include the additional services. Note that this edition has been retired in SQL Server 2012.^[9]

Express

SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 10 GB database files (4 GB database files prior to SQL Server Express 2008 R2).^[10] It is intended as a replacement for MSDE. Two additional editions provide a super set of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic. SQL Server Express with Advanced Services adds full-text search capability and reporting services.

2. What is default size of the SQL database ?

On a separate note, with the default installation of SQL Server the mdf file of model database is 3 MB and ldf is about 1 MB.

3. What is default TCP/IP port for SQL SERVER ?

By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. we can choice our own port also.

4. What are the SQL Server system Database ?

SQL Server System Databases

• Master
  • Purpose - Core system database to manage the SQL Server instance.  In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema.  In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
  • Prominent Functionality
    • Per instance configurations
    • Databases residing on the instance
    • Files for each database
    • Logins
    • Linked\Remote servers
    • Endpoints
  • Additional Information
    • The first database in the SQL Server startup process
    • In SQL Server 2005, needs to reside in the same directory as the Resource database
    • Sources for Database Information - SQL Server 2000 to 2005 Crosswalk
    • SQL 2000 to 2005 Crosswalk - Startup Parameters
• Resource
  • Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
  • Prominent Functionality
    • System object definition
  • Additional Information
    • Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
    • Prior to SQL Server 2005 the system related data was stored in the master database
    • Read-only database that is not accessible via the SQL Server 2005 tool set
    • The database ID for the Resource database is 32767
    • The Resource database does not have an entry in master.sys.databases
• TempDB
  • Purpose - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
  • Prominent Functionality
    • Manage temporary objects listed in the purpose above
  • Additional Information
    • Properly Sizing the TempDB Database
    • Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
• Model
  • Purpose - Template database for all user defined databases
  • Prominent Functionality
    • Objects
    • Columns
    • Users
  • Additional Information
    • User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
    • The database configurations such as the recovery model for the Model database are applied to future user defined databases
• MSDB
  • Purpose - Primary database to manage the SQL Server Agent configurations
  • Prominent Functionality
    • SQL Server Agent Jobs, Operators and Alerts
    • DTS Package storage in SQL Server 7.0 and 2000
    • SSIS Package storage in SQL Server 2005
  • Additional Information
    • Provides some of the configurations for the SQL Server Agent service
    • For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
    • Missing SQL Server Agent History
    • MSSQLTips Category - SQL Server Agent
• Distribution
  • Purpose - Primary data to support SQL Server replication
  • Prominent Functionality
    • Database responsible for the replication meta data
    • Supports the data for transaction replication between the publisher and subscriber(s)
  • Additional Information
    • MSSQLTips Category - Replication
• ReportServer
  • Purpose - Primary database for Reporting Services to store the meta data and object definitions
  • Prominent Functionality
    • Reports security
    • Job schedules and running jobs
    • Report notifications
    • Report execution history
  • Additional Information
    • MSSQLTips Category - Reporting Services
• ReportServerTempDB
  • Purpose - Temporary storage for Reporting Services
  • Prominent Functionality
    • Session information
    • Cache
  • Additional Information
    • MSSQLTips Category - Reporting Services

5. What are the authentication modes in SQL Server ? How can it be changed ?

Please check this Link :     Authentication in SQL SERVER

6. What is Query execution Plan in SQL SERVER ?

The query execution plan is a useful tools offered by SQL Server. It describes the steps and order of a SQL query during executions. After that a DBA can decide which part of the query is slow. SQL Server can create execution plans in two ways :

Actual execution Plan : (CTRL+M) :-
It is created after execution of the query and contains the steps that were performed.

Estimate Execution Plan : (CTRL+L) :-
It is created without executing the query.

7. SQL Server Replication ?

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.