SQL Server 2008 Interview Questions for Administrators
Below is a collection of SQL Server 2008 interview questions covering the administration side of SQL Server. Including questions on admin topics such as DBCC operations, backup and restore, admin commands, installation, upgrades and general support.
They are suitable for the following job roles:
- SQL Data Analyst
- Database Administrator
- BI Support Developer
- SQL Data Mining
- SQL Server Consultant
- IT Support
- SQL Server Developer
- SQL DBA
We wrote these questions for other IT professionals to help them practice for their next interview or technical test. They are suitable for both consultants, temp staff or full time permanent roles. If the organisation is using SQL Server 2008 then these questions should help you out..
Interview Questions
1. SQL Server can be installed via the command prompt by calling Setup.exe and specifying parameters including the INSTANCENAME. Which of the following services are instance aware?
Database Engine, SQL Server Agent, Reporting Server, and Analysis Services
Database Engine, SQL Server Agent, Reporting Server
Database Engine, SQL Server Agent
Just Database Engine
Show AnswerAnswer:
Database Engine, SQL Server Agent, Reporting Server, and Analysis Services
Comments:
The Database Engine, SQL Server Agent, Reporting Server, and Analysis Services are all instance aware. The other components, which are not instance aware, require the INSTANCENAME token when running from the command prompt.
2. A member of staff has requested that you install a SQL tool for data mining. They want to use the tool to identify rules and patterns in the company data and determine why things happen and predict what will happen in the future. Which installation component should you install to meet their requirements?
Analysis Services
Database Engine
SQL Agent
Reporting Server
Show AnswerAnswer:
Analysis Services
Comments:
Analysis Services (SSAS) provides a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining
3. Someone from your organisation is complaining that they can’t connect to the HR SQL Server using SQL Management Studio. However, they can open the Management Studio ok and other people can connect to the server fine. What might be the problem?
SQL Client tools have not been installed on the users machine
None of these
The main server is probably down and needs rebooting
The SQL Agent is not running
Show AnswerAnswer:
The SQL Agent is not running
Comments:
The SQL Agent must be running on the users machine to connect to an instance of SQL Server. This is a local service, which can be configured to start automatically. If it has stopped, just start the service from the Control Panel - Services console.
4. You want to administer the SQL services that automatically start when a user starts their PC. SQL 2008 comes with a built in tool which allows you to changes services accounts and auto-start services. Which tool is this?
SQL Server Configuration Manager
SQL Server Surface Area Configuration
Notification Services Command Prompt
SQL Management Studio
Show AnswerAnswer:
SQL Server Configuration Manager
Comments:
The SQL Server Configuration Manager tool enables you to specify SQL Server Services options and whether the service starts automatically or manually. You can also stop and start services using this tool.
5. The tempbd plays an even more important role than in previous versions of SQL Server. This includes DBCC operations, event notifications, large object variables and parameters. Its important to ensure that certain settings are enabled for the tempdb database. Which two are likely settings that you should configure?
Auto growth disabled, Full Recovery model selected
Auto growth enabled, Simple Recovery model selected
Auto growth enabled, Compatibility level set to SQL 7
Auto growth enabled, Full Recovery model selected
Show AnswerAnswer:
Auto growth enabled, Simple Recovery model selected
Comments:
You also want to ensure that enough space has been preallocated to the tempdb to ensure that most operations are handled. You do this by setting the file size to an appropriate value. Autogrow should be enabled be default but autogrow operations are expensive and time consuming so its best to preallocate space
6. When you uninstall SQL 2008, you will find that certain files are not removed by the uninstaller. Which files/programs do you normally have to remove manually?
Log files
The 100 directory under program files/MS SQL Server
.NET framework 3.5
All of these
Show AnswerAnswer:
All of these
7. You are in the process of installing SQL 2008 as a a side-by-side upgrade. You want to migrate all databases from SQL 2005 to this new instance. Which method could you use to achieve this?
Any of these options
Backup the database and restore o the new SQL 2008 instance
Detach the databases from the old instance and reattach to the new instance
Use the Copy Database Wizard to migrate databases
Show AnswerAnswer:
Any of these options
8. Side by side upgrades are a common choice when installing SQL 2008 alongside an older version of SQL Server such as 2000 or 2005. Which of the following is a not an advantage of SQL side by side upgrades?
Best suited for very large databases
Instance naming for connecting applications do not change
Ability to upgrade platforms from 32 to 64 bit
Ability to run SQL Servers side by side for testing and verification
Show AnswerAnswer:
Instance naming for connecting applications do not change
Comments:
You should consider several factors before choosing an upgrade strategy. Your strategy should include the need for component-level upgrade, ability to roll back in case of failure, the size of databases, and whether a partial upgrade is needed
9. You just opened SSMS to connect to the company server to do a manual backup. Your connection request waits and then eventually times out. You cannot get connected no matter how hard you try. Your colleague thinks that the problem is limited resources on the server preventing any additional connections. If the name of the server is SQL1 how can you still connect to the server using SSMS?
SQL1:admin
override:SQL1
DAC:SQL1
admin:SQL1
Show AnswerAnswer:
admin:SQL1
Comments:
The Dedicated Administrator Connection (DAC) enables you to connect and do your work on almost all occasions. To connect to the DAC using SSMS, you add the admin prefix to the server name
10. You are worried that your SQL 2008 master database is corrupted. However, your manager was smart enough to keep a temporary configuration of the master database that is not corrupt. Which flags should you use to run SQLServr.exe to point it to an alternative master database?
e and m
f and h
n and x
d and l
Show AnswerAnswer:
d and l
Comments:
SQLServr.exe [-d master_file_path] [-l master_log_path]
11. An database administrator working at your company has defined a startup stored procedure that is causing a problem preventing SQL Server from starting. Not only is he in big trouble but you’ve got to find a way round this. Which flag should you use to start SQL 2008 in minimal mode?
m
d
h
f
Show AnswerAnswer:
f
Comments:
The f flag starts SQL Server 2008 in minimal mode (if used from the command prompt). This ensures that only one connection is allowed to the server, giving you a chance to fix any problems.
12. You want to modify the server settings without using the Server properties screen from the Management Studio. Which stored procedure allows you to change server settings?
sp_bindrule
sp_autostats
sp_addtype
sp_configure
Show AnswerAnswer:
sp_configure
Comments:
sp_configure displays or changes global configuration settings for the current server. When executed with no parameters, sp_configure returns a result set with five columns and orders the options alphabetically in ascending order, as shown in the following table
13. Your manager is testing your experience of SQL Server 2008 and stored procedures. He wants you to show him which active users are connected to the server, which tasks they are performing and also disk IO usage. However, you can’t use the Activity Monitor window in SMSS. Which sp should you use?
sp_who2 55
sp_who2 ‘active’
sp_who ‘active’
sp_who
Show AnswerAnswer:
sp_who2 ‘active’
Comments:
Only the sp_who2 command used in conjunction with ‘active’ will show you active connections and also disk IO usage
14. SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Which of the following is a component of SQL Server Agent?
All of these
Jobs – work to be done
Schedules – when job is executed
Alerts – notification when event occurs
Show AnswerAnswer:
All of these
Comments:
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task
15. SQL Server Agent can be used to run and schedule jobs that perform one or more tasks. How can you run a job using SQL Server Agent?
Attaching the job to a schedule
In response to an alert
Any of these options
Show AnswerAnswer:
Any of these options
Comments:
Manually via SQL Server Management Studio (SMSS)