Luxoft Most Frequently Asked Latest SQL Server Interview Questions Answers
What Purpose Does The Model Database Server?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
How Do You Trace The Traffic Hitting A Sql Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?
There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here
Database Partitioning
Dynamic Management Views
System Catalog Views
Resource Database
Database Snapshots
SQL Server Integration Services
Support for Analysis Services on a a Failover Cluster.
Profiler being able to trace the MDX queries of the Analysis Server.
Peer-toPeer Replication
Database Mirroring
What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?
Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
How Do You Troubleshoot Errors In A Sql Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
What Is The Default Port No On Which Sql Server Listens?
1433
What Is Transparent Data Encryption?
Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
What Are The Operating Modes In Which Database Mirroring Runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
What Is The Difference Between The 2 Operating Modes Of Database Mirroring?
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
A Database can contain a maximum of 32,767 files.
There are Primarily 2 types of data files Primary data file and Secondary data file(s)
There can be only one Primary data file and multiple secondary data files as long as thetotal # of files is less than 32,767 files.
What Purpose Does The Model Database Server?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
How Do You Trace The Traffic Hitting A Sql Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?
There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here
Database Partitioning
Dynamic Management Views
System Catalog Views
Resource Database
Database Snapshots
SQL Server Integration Services
Support for Analysis Services on a a Failover Cluster.
Profiler being able to trace the MDX queries of the Analysis Server.
Peer-toPeer Replication
Database Mirroring
What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?
Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
 |
| Luxoft Most Frequently Asked Latest SQL Server Interview Questions Answers |
How Do You Troubleshoot Errors In A Sql Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
What Is The Default Port No On Which Sql Server Listens?
1433
What Is Transparent Data Encryption?
Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
What Are The Operating Modes In Which Database Mirroring Runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
What Is The Difference Between The 2 Operating Modes Of Database Mirroring?
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.
How Many Files Can A Database Contain In Sql Server?how Many Types Of Data Files Exists In Sql Server? How Many Of Those Files Can Exist For A Single Database?
What Is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
What Is The Default Fill Factor Value?
By default the fill factor value is set to 0.
Where Do You Find The Default Index Fill Factor And How To Change It?
The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes. The other option of viewing and changing this value is using
What Is A System Database And What Is A User Database?
System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.
A Database can contain a maximum of 32,767 files.
There are Primarily 2 types of data files Primary data file and Secondary data file(s)
There can be only one Primary data file and multiple secondary data files as long as thetotal # of files is less than 32,767 files.