techpreparation-homepage

Home  Interview Questions  Certifications  Aptitude Questions  Tutorials  Placement Papers  Search  Resume  Soft Skills  Video  Forum  Blog

Android app on Google Play

Technical Interview Questions
Oracle Interview Questions
MySql Interview Questions
MsSql Interview Questions
JDBC Interview Questions
                              .........More

Soft Skills
Communication Skills
Leadership Skills
                              .........More

 

 

  

Microsoft SQL Server Interview Questions and Answers



In SQL Server 2005, you want to monitor your transactional replication and determine how long it takes for a transaction to move from the publisher to a subscriber. What would you use to help you do this?
Tracer Tokens.
You can use tracer tokens in SQL Server 2005 replication to measure latencies. These are small amounts of data written into your transaction logs.

How many credentials can one login be mapped to?

1
A credential can be mapped to many logins, but a login can only be mapped to one credential.

You are looking to secure your publisher to prevent unauthorized logins from accessing the data through replication. Where do you add the list of logins that have access?
Add them to the Publication Access List in the publication database.
All access to publications is controlled with the Publication Access List (PAL), which is similar to the Windows ACLs. A list is created for each publication that is set up.

What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH construct and returns the name of the procedure that caused an error to be thrown.

Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL Server. It contains the authentication information needed for accessing these resources.

Which of the following is an invalid file name for a database file?
None of the above
Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use.

What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection. This is returned as integer and each bit in the integer represents an option.

You have setup forwarding for your SQL Server 2005 Service Broker queues to another server. You edit a route to move a queue to a new server. What do you need to do now to ensure you can recover the routes in the event of a disaster?
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb database, the routes will be captured.

You are looking to setup a large Notification Services application and you are planning on a 40GB database. When installing Notification Services, the creation fails. What is the likely problem?
Notification Services installation has a 10 minute timeout and the database creation probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the database takes longer to create, the installation of the instance fails. You need to reduce the size of the database for installation and then increase it when you are done.

You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM on one of your servers. An ASP.NET application on the same server is feeling memory pressure and you want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down and restart SQL Server to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server is restarted.

You are troubleshooting a Service Broker application and find that messages are remaining on the queue. You determine that there is no active queue monitor. What should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an active queue monitor. If one is not on, then it needs to be activated. The ALTER QUEUE statement is used to change this.

You are implementing replication across the Internet for a large bank that wishes to move up to date pricing information to an analyst's laptop. They are concerned about spoofing of their main site by criminals. What can you do to secure the replication solution with SQL Server 2005?
Set the encryption level to verify the certificate is issued by a trusted authority.
Since you are asked to use replication, a replication solution is to use encryption for the connection and verify the certificate being used is from a trusted authority. Setting the encrpytion level to 2 will do this.

In SQL Server 2005, you are looking to implement full-text search. One of the tables you are looking to index stores Mircrosoft Word documents in a varbinary(max) column. Can you use Full-text search to index this column?
Yes
You can use full-text search for formatted data such as Word that contains text stored in a varbinary column.

Page Numbers :  1       2        3       4       5       6       7       8        9       10         11       12       13       14       15       16

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Job Interview Questions for more Interview Questions with Answers