Staying up on the latest SQL Server technologies is a huge benefit to employers. Many seek only the best and brightest to build solutions for the organization. The general premise is the more knowledgeable their team is the more opportunities the organization will have to succeed. With the release of SQL Server 2005 many new features were introduced, but not taken advantage of across the industry for one reason or another. Break out of your SQL Server 2000 shell and test your knowledge on the SQL Server 2005 new feature set.
In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer below the question to see how you did. Good luck!
Question Difficulty = Easy
Question 1: True or False - SQL Server 2005 was the first version of SQL Server supporting 64 bit builds.
False - SQL Server 2000 supports 64 bit builds of the relational engine.
Question 3: Can you explain how to deploy an SSIS package?
A few different options exist to deploy SSIS packages. The first option is to the build a deployment folder then copy and paste the directory to the intended SQL Server instance. Once on the instance, it is necessary to run deployment manifest wizard to install the SSIS Package. The second option is to use Import or Export Package option in SQL Server Management Studio. A third option is to use the Save Copy of Package in the Business Intelligence Development Studio. A final option is to use the dtutil command line utility to save the package to the SQL Server instance.
Question 2: With SQL Server 2005, what is the new technique to manage errors in Integration Services?
With SQL Server 2005 Integration Services, Event Handlers were introduced. These can be setup to capture errors and perform a specific operation such as writing the errors to a table. Additionally, custom logging can be setup at the Control Flow level and perform custom logging as well.
Question 3: What was the new replication architecture introduced with SQL Server 2005 and can you explain how it works?
Peer to Peer replication was introduced with SQL Server 2005.
Peer to Peer replication consists of two or more peer nodes. Every node acts as both a publisher and subscriber with the ability to change data on any node with the changes replicated to the remainder of the nodes.
Question 4: Can you explain the difference between the INTERSECT and EXCEPT operators? What is the value of these commands over other techniques?
INTERSECT - gives you the final result set where values in both of the tables match
EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset
The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.
Question 2: What was the service oriented architecture (SOA) technology introduced with SQL Server 2005 and what are some of the components of the technology?
Service Broker is the service oriented architecture (SOA) technology introduced with SQL Server 2005.
Service Broker components include:
Endpoint - Communication point in the database for Service Broker. In addition, ability to specify the authentication, encryption and message forwarding. A single Service Broker endpoint can be created for each database, however the HTTP and Database Mirroring can be created as well.
Message Type - Format for the message that is being sent and received. The format could be simply well formed XML or be bound to a schema.
Contract - Which message types are sent by either the initiator or the target.
Route - For the specific Service Broker Service (see below) which SQL Server instance and database the messages will be routed to during the Conversation (see below).
Queue - At a high level, this is the logical container in the Service Broker application. The queues serve as a storage mechanism in Service Broker. All data is RECEIVED (see below) from the queue for processing. Although the queues are all named differently, they are actually all of the same format with both relational and binary columns that can be converted to an XML format.
Service - The Service correlates the queue and the contract.
Remote Binding Service - Defines the Service Broker user to with the Service.
Question 3: What is the OUTPUT clause and what sorts of options does it provide to applications?
The OUTPUT clause is new to SQL Server 2005 and has the ability to access the INSERTED and DELETED tables as is the case with a trigger. The OUTPUT command can be added to your stored procedures or T-SQL scripts in order to write the data out to an auditing table or return the data back to the front end client.
The OUTPUT clause has the ability to mimic and/or replace some of the functionality typically addressed by triggers.
Question 5: Can you explain the new Disaster Recovery (DR)\High Availability (HA) solution introduced with SQL Server 2005? Can you outline some of the benefits of this technology?
Database mirroring was introduced in SQL Server 2005 as a new means to protect against data loss.
From an architecture perspective, Database Mirroring consists of two mandatory roles and an optional third role. The Principal Role and the Mirror Role are mandatory. These roles would be installed on the Enterprise and Standard editions of SQL Server 2005. The third and optional role is the Witness role which can be installed on an any version of SQL Server including Express Edition.
In terms of data protection, Database Mirroring has three different options. First is High Availability Operating Mode. High Availability Operating Mode provides durable, synchronous transfer of data between the principal and mirror instances including automatic failure detection and failover. Second is High Performance Operating Mode. With the High Performance Operating Mode the overall architecture acts as a warm standby and does not support automatic failure detection or failover. Third is High Protection Operating Mode. The High Protection Operating Mode operates very similar to the High Availability Mode except the failover and promotion (mirror to principal) process is manual.
As you prepare for an upcoming SQL Server Developer or DBA technical interview, review the SQL Server interview questions in this tip as a means to prepare for the technical portion of the interview. As you continue to prepare for the interview, check out all of the MSSQLTips.com Interview Question tips.
Stay tuned for future SQL Server interview questions related to new SQL Server 2008 features.
If you have some key interview questions related to SQL Server 2005 that you always include in your interview process, please share your knowledge with the community by posting the questions in the forum below.
Last Update: 5/14/2008
About the author
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.