Adding users in SQL Server is one of the most funtamental tasks you'll perform. This article describes in great detail the different steps you have to take to establish a new user in SQL Server.
Have you ever needed moving the database from one location to another? In SQL Server, you will need functionality to detach and attach your database in order to move it from one location to another. Here you will find a thorough description of what you'll need to do.
As a DBA, you will very likely need to move bulks of data from one database to another, or make a copy of your data. This article provides a step-by-step tutorial on Import and Export in SQL Server
Today's database systems need mechanisms to increase performance. One of the most important things you can do, is adding SQL Server indexes
Scheduling jobs is a very useful functionality of the SQL Server database. There are several uses of this feature. Read more in this article on SQL Server jobs
Preparing a maintenance plan is an important task, but SQL Server provides you with a wisard for such a plan. Read this tutorial on how to set up a SQL Server Maintenance Plan
Every database needs a backup copy. Hopefully, you will never need it, but all practice has learnt us that a backup can come in very handy. Read this tutorial on how to produce a SQL Server backup
When disaster happens, you have of course a disaster plan and a backup of the database?. If so, it is easy to perform a SQL Server Restore Database operation.
Sooner or later, you will most likely have to search for performance bottlenecks, or perform other monitoring tasks. This article is a simple tutorial on how to use SQL Server Profiler
If your database should go down, you need to bring it back to as current state as possible. In case of a system failure, the SQL Server Transaction Log will prove invaluable
SQL Server Architecture
SQL Server Version’s and Editions
SQL server Installation and Configuration
Pages and extents
Sql server services
Sql server protocols
Security-Login and User, Principals, Securable, Managing Permissions
Isolation Levels and Concurrency
Locking and Blocking-Types of locks and Types of reads
Service accounts for SQL server
GUI Templates-object explorer, solution explorer, template explorer, registered servers
Command line tools-BCP, SQLCMD
Creating and Configuring Database
Altering Databases and Their Options-Using ALTER command, Controlling Access, Controlling DB state.
DAC-Dedicated Administrator Connection
CMS-Central Management Server
Data Compression and Backup Compression
Using ALTER Authorization Command
Schemas-Creating and working with schemas
DBCC-And their parameters
Indexes-Types of Indexes, working and maintaining indexes, resolving index fragmentation, fill factor
SQL Server 2008 Top New Features
DMV’s-Types of Dynamic Management Views and working with them.
PBM-Policy Based Management
Performance data collector
SQL Server profiler
Types of Sql Server Backups and their important options.
Types of Restores and its related options
Orphan Users-Fixing orphan users
Basics of Windows Clustering
Overview of real time Third party Monitoring toolsEx:-Idera DM, Lite speed.
Overview of real time Ticketing tool-Ex:-Altiris, Remedy.
1. Explain about your SQL Server DBA Experience.
2. What are the different SQL Server Versions you have worked on?
3. What are the different types of Indexes available in SQL Server?
4. What is the difference between Clustered and Non-Clustered Index?
5. What are the new features in SQL Server 2005 when compared to SQL Server 2000?
6. What are the High-Availability solutions in SQL Server and differentiate them briefly.
7. How do you troubleshoot errors in a SQL Server Agent Job?
8. What is the default Port No on which SQL Server listens?
9. 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?
10. What is DCL?
11. What are the commands used in DCL?
12. What is Fill Factor?
13. What is the default fill factor value?
14. Where do you find the default Index fill factor and how to change it?
15. What is a system database and what is a user database?
16. What are the recovery models for a database?
17. What is the importance of a recovery model?
18. What is Replication?
19. What the different types of Replication and why are they used?
20. What the different components in Replication and what is their use?
21. What the different Topologies in which Replication can be configured?
22. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?
23. What are the different Authentication modes in SQL Server and how can you change authentication mode?
24. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
25. What is meant by Active – Passive and Active – Active clustering setup?
26. List out some of the requirements to setup a SQL Server failover cluster.
27. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
28. How do you open a Cluster Administrator?
29. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?
30. What are the different ways you can create Databases in SQL Server?
31. When setting Replication, can you have Distributor on SQL Server 2005, Publisher on SQL Server 2008?
32. When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.
33. What is the difference between dropping a database and taking a database offline?
34. Which autogrowth database setting is good?
35. What are the different types of database compression introduced in SQL Server 2008?
36. What are the different types of Upgrades that can be performed in SQL Server?
37. What is Transparent Data Encryption?
38. Does Transparent Data Encryption provide encryption when transmitting data across network?
39. What are the operating modes in which Database Mirroring runs?
40. What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?
41. What purpose does the model database serve?
42. How do you trace the traffic hitting a SQL Server?
43. What types of replication are supported in SQL Server?
44. Why would you use SQL Agent?
45. What happens on checkpoint?
46. What is DBCC?
47. How can you control the amount of free space in your index pages?
48. Why would you call Update Statistics?
49. What is a correlated sub-query?
50. What authentication modes does SQL Server support?