1. What is purpose of Registered Servers ?
Used to store the server connection information for future connections.
2. How to enable multiple processors to SQL Server ?
Right Click on SQL Server Instance --> Properties --> Processors --> Enable Processors
3. What is Linked Server ? How to connect Linked Server ? How to test Linked Server ?
One server connected with another server to execute queries against OLEDB data sources on remote server .
OLEDB Provider :
An OLEDB provider is a DLL that manages and interacts with a specific data sources such as SQL Server 7.0 , Access , Excel , ODBC , Oracle , DB2 , Local File System , Exchange Server etc .
SQL Native Client (PROGID :SQLNCLI) is the official OLEDB provider for SQL Server .
To connect Linked Server using OLEDB Provider :
sp_addlinkedserver @server='servername' , @srvproduct='SQL Server / Oracle '
How to test the connection to a linked server :
sp_testlinkedserver <ServerName>
4. What is Fill Factor ? How to assign Fill Factor ?
A fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits .
Assign Fill Factor :
Right Click On Server --> Properties --> Database Setting --> Default Index Fill Factor --> Provide Value .
Recommended fill factor value is 70-80%
5. What are the protocols used in networking ? What is the default port no of TCP / IP ? Can we change the port number ?
The protocols used in networking are TCP / IP , Named Pipes , VIA , Shared Memory .
The default port no of TCP / IP is 1433 . According to the company requirements we can change port numbers .
6. What is Max Degree of Parallelism ? When it will set up ?
7 . What is AWE (Address Windowing Extensions ) ? How to configure Dynamic Memory Allocation ?
Generally SQL Server reserves only a small portion of memory . Some times reserved memory not sufficient to SQL Server and required additional memory . AWE is used to support very large amounts of physical memory . When AWE is enabled the operating system dynamically allocates memory to SQL Server based on the min server memory and max server memory settings .
In Windows Server 2003 Standard Edition supports upto 4 GB , Enterprise Edtion supports upto 32 GB and Data Center supports 64 GB .
To Configure AWE : Right click on Instance --> Properties --> Memory --> Enable AWE --> Set Mine Memory and Max memory etc .
Note : AWE is not needed and cannot be configured on 64 - bit operating system .
8. In which environments we can use simple recovery model to the databases ?
Simple recovery model is useful for Development , Test , Datawarehouses or not often changed environments .
9 . In which recovery model Point-in-time recovery model is possible ?
Point-in-time recovery model is possible only in Full and Bulk-Logged recovery models but in bulk logged recovery model Point-in-time recovery may or may not possible because we will lost bulk operations those are not recorded in to the log .
10. Why can't take T.Log backups in simple recovery model ?
In simple recovery model the transaction logs are truncated .
11. What is use of Recovery Models ?
Recovery models are designed to control transaction log maintenance .
12. What is Tail Log backup ?
Tail Log backup is the last backup that is used to recover the database to the point of failure . Supports only Full or Bulk Logged recovery models.
Situation to perform tail-log backup
Assume that 4 PM log backup is performed and 5 PM log backup has to be performed . In this case if the database crashed at 4:30 PM so here 30 mins of data may loss . So we use tail log backup to recover this 30 mins of data .
13. Can we take Diff / T.Log backups without Full Backups ?
No , Its not possible because Full backup is the base backup for Diff / T.Log backups .
14. What is the use of Copy - Only backup ?
Copy only backup is new feature in SQL Server 2005 and they do not affect overall backup and restore procedure . Copy Only backup cannot affect on log sequence number .
15. How to automate backups on schedule basis ?
We can create maintenance plans or any third party tool for taking backups on schedule basis .
No comments:
Post a Comment