Sunday, August 24, 2014

SQL DBA Interview Questions

http://www.mssqltips.com/sqlservertip/1626/junior-sql-server-dba-interview-questions/

Problem
My organization is in the process of hiring a junior SQL Server DBA to add to our team.  What are some fair questions to ask as a portion of the interview process?  I want to make sure we hire someone who has a good base of knowledge.  I am concerned our Senior SQL Server DBAs are looking to stump the candidates which is not really my goal.  Do you have any suggestions?
Solution
Trying to assess any technical person's skill set can be difficult.  An interview process with appropriate questions for your environment and the correct skill set are key.  In some respects understanding what a candidate knows and does not know could be equally beneficial.  What I mean by that is you want to know what knowledge someone has on day one and what you will need to teach them over time.  As such, here are a baseline set of questions for a junior SQL Server DBA.

SQL Server Backup and Recovery

  • Question 1 - What are 2 options to validate whether or not a backup will restore successfully?

SQL Server Performance Tuning

  • Question 1 - Name as many native SQL Server performance monitoring and tuning tools that you know of and their associated value.
    • System objects - System objects such as sp_who2, sp_lock, fn_get_sql, etc. provide a simple means to capture basic metrics related to locking, blocking, executing code, etc.
    • Profiler - In a nutshell, Profiler provides the lowest common denominator of activity on a SQL Server instance.  Profiler captures per session code with the ability to filter the data collection based on database, login, host name, application name, etc. in order to assess the IO, CPU usage, time needed, etc.
    • Perfmon\System Monitor - Perfmon\System Monitor is responsible for macro level metrics related to processes and sub systems.
    • Dynamic Management Views and Functions - New to SQL Server 2005 and beyond, the Dynamic Management Views and Functions offer a real time view into the SQL Server sub systems.
    • TYPEPERF.EXE - TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. It is necessary to capture performance data whenever you are trying to diagnose performance issues on a server. Performance data provides information on the server's utilization of the processor, memory, and disk, as well as SQL Server-specific performance data.
    • SQL Server Management Studio Built-in Performance Reports - As part of the installation of SQL Server 2005 and beyond a number of performance-related reports are installed. To get to these reports open the SQL Server Management Studio (SSMS) and connect to a SQL Server instance. If you don't have an instance of Reporting Services installed then the icon will be disabled.
    • Additional resources for this question:

  • Question 2 - How do you go about tuning a SQL Server query?
    • Identify the query causing the issue.
    • Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical Query Plan or sys.dm_exec_query_stats.
    • Review the individual query components to determine which components of the query have the highest cost.
    • Outline options to improve the query such as moving from cursor based logic to set based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY clause, adding indexes, removing indexes, creating covering indexes, etc.
    • Test the options to determine the associated performance improvement.
    • Implement the solution.

SQL Server Maintenance


  • Question 2 - Name 3 or more DBCC commands and their associated purpose.
    • DBCC CACHESTATS - Displays information about the objects currently in the buffer cache.
    • DBCC CHECKDB - This will check the allocation of all pages in the database as well as check for any integrity issues.
    • DBCC CHECKTABLE - This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
    • DBCC DBREINDEX - This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
    • DBCC PROCCACHE - This command will show you information about the procedure cache and how much is being used. 
    • DBCC MEMORYSTATUS - Displays how the SQL Server buffer cache is divided up, including buffer activity.
    • DBCC SHOWCONTIG - This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
    • DBCC SHOW_STATISTICS - This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
    • DBCC SHRINKFILE - This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
    • DBCC SQLPERF - This command will show you much of the transaction logs are being used.
    • DBCC TRACEON - This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
    • DBCC TRACEOFF - This command turns off a trace flag.

SQL Server Database Design

  • Question 1 - What happens when you add a column in the middle of a table (dbo.Test1) in SQL Server Management Studio?
    • Management Studio creates a temporary table called dbo.Tmp_Test1 with the new structure.
    • If there is data in the original table dbo.Test1 this data is inserted into the new temp table dbo.Tmp_Test1 (now you have two sets of the same data).
    • The original table dbo.Test1 is dropped.
    • The new table dbo.Tmp_Test1 is renamed to dbo.Test1.
    • If the table has indexes all of the indexes are recreated.

  • Question 2 - What are included columns with respect to SQL Server indexing?
    • A new type of index was developed in SQL Server 2005 and beyond that assists in situations where a covering index is needed. 
    • Indexes with Included Columns are nonclustered indexes that have the following benefits:
      • Columns defined in the include statement, called non-key columns, are not counted in the number of columns by the database engine.
      • Columns that previously could not be used in queries, like nvarchar(max), can be included as a non-key column.
      • A maximum of 1023 additional columns can be used as non-key columns.
    • Additional information - Improve Performance with SQL Server 2005 Covering Index Enhancements

SQL Server Business Intelligence

  • Question 1 - Name some new features from Data Transformation Services to SQL Server Integration Services.
    • SSIS checkpoints.
    • SSIS logging.
    • SSIS package configurations.
    • SSIS Breakpoint.
    • Dynamic flat file connections.
    • SSIS batch processing.
    • MERGE JOIN.
    • Additional information - SQL Server Integration Services

Next Steps
  • To the interviewers:
    • As you prepare for a junior level SQL Server interview, be sure to have appropriate questions in place to challenge the candidate and understand their skill level on any given topic.
    • Be sure to ask questions to understand the depth and breadth of the candidates knowledge base.
  • To the interviewees:
    • Be prepared for your technical interviews.  The questions in this tip are intended for a newbie SQL Server DBA, but more often than not, you will get some very challenging questions and scenarios from interviewers.  Many of these situations and questions will probably not be familiar to you, so think quickly and be sure to prepare a response to questions you simple do not know the answer to.
  • Check out these related tips:

http://www.indiabix.com/technical/sql-server-common-questions/6

1. Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
2. What are the difference between clustered and a non-clustered index?
  1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
  2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
3. What are the different index configurations a table can have?
A table can have one of the following index configurations:
  1. No indexes
  2. A clustered index
  3. A clustered index and many nonclustered indexes
  4. A nonclustered index
  5. Many nonclustered indexes
4. What are different types of Collation Sensitivity?
  1. Case sensitivity - A and a, B and b, etc.
  2. Accent sensitivity
  3. Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
  4. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.
5. What is OLTP (Online Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
6. What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

7. What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
  1. TRUNCATE:
    1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
    2. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
    3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
    4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
    5. TRUNCATE cannot be rolled back.
    6. TRUNCATE is DDL Command.
    7. TRUNCATE Resets identity of the table
  2. DELETE:
    1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
    2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
    3. DELETE Can be used with or without a WHERE clause
    4. DELETE Activates Triggers.
    5. DELETE can be rolled back.
    6. DELETE is DML Command.
    7. DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
8. When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
10. What are the properties and different Types of Sub-Queries?
  1. Properties of Sub-Query
    1. A sub-query must be enclosed in the parenthesis.
    2. A sub-query must be put in the right hand of the comparison operator, and
    3. A sub-query cannot contain an ORDER-BY clause.
    4. A query can contain more than one sub-query.
  2. Types of Sub-Query
    1. Single-row sub-query, where the sub-query returns only one row.
    2. Multiple-row sub-query, where the sub-query returns multiple rows,. and
    3. Multiple column sub-query, where the sub-query returns multiple columns
11. What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.
12. What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.
13. Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').
14. What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.
15. Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
16. What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
17. Name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
18. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

19. What is the difference between a Local and a Global temporary table?
  1. A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
  2. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
20. What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.
21. What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
22. What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
23. What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
24. What is CHECK Constraint?
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
25. What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
26. How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
27. What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
28. What are the advantages of using Stored Procedures?
  1. Stored procedure can reduced network traffic and latency, boosting application performance.
  2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  3. Stored procedures help promote code reuse.
  4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
  5. Stored procedures provide better security to your data.
29. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.
30. Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group


31. What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
32. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
33. What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.


http://learnsqlwithbru.com/sql-server-interview-questions-and-answers/sql-server-dba-q-and-a/

SQL Server DBA Q and A

This page contains SQL Server Database Admin Interview Questions for experienced, I am sure you must have read fundamental questions  like What is RDBMS, What is a view, What is a database etc etc… This list of SQL Server DBA Questions and Answers has less of those questions and more stuff that is expected to be asked in DBA interviews…
I’m sure you will find plenty of new Interview Questions and Answers for SQL Server 2008 and 2008 R2 that are useful for your Interview preparation.
1. Explain about your SQL Server DBA Experience.
  • This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.
2. What are the different SQL Server Versions you have worked on?
  • The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
3. What are the different types of Indexes available in SQL Server?
  • The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
4. What is the difference between Clustered and Non-Clustered Index?
  • In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
  • In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
5. 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
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?
  • 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.
8. What is the default Port No on which SQL Server listens?
  • 1433
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?
  • 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 the total # of files is less than 32,767 files
Added on Dec 30th 2010
10. What is DCL?
  • DCL stands for Data Control Language.
11. What are the commands used in DCL?
  • GRANT, DENY and REVOKE.
12. 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.
13. What is the default fill factor value?
  • By default the fill factor value is set to 0.
14. 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 sp_configure.
Added on Oct 29th 2011
15. 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.
16. What are the recovery models for a database?
  • There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.
17. What is the importance of a recovery model?
  • Primarily, recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.
Added on Nov 9th 2011
18. What is Replication?
  • Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.
19. What the different types of Replication and why are they used?
  • There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose, depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of  remote / distributed systems where the data flow can be from multiple sites, for example sales done at a promotional events which might not be connected to the central servers always..
20. What the different components in Replication and what is their use?
  • The 3 main components in Replication are Publisher, Distributor and Subscriber. Publisher is the data source of a publication. Distributor is responsible for distributing the database objects to one or more destinations. Subscriber is the destination where the publishers data is copied / replicated.
21. What the different Topologies in which Replication can be configured?
  • Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:
  • Publisher, Distributor and Subscriber on the same SQL Instance.
  • Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
  • Publisher, Distributor and Subscriber on individual SQL Instances.
Added on Nov 12th 2011
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?
  • I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services / components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.
23. What are the different Authentication modes in SQL Server and how can you change authentication mode?
  • SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode. To change the Authentication mode, read one of my blogs Changing SQL Server Authentication Mode.
The following Question and Answers on SQL Server High Availability were Added on Nov 28th 2011
24. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
  • On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…
25. What is meant by Active – Passive and Active – Active clustering setup?
  • An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
  • An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.
26. List out some of the requirements to setup a SQL Server failover cluster.
  • Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
27. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
  • Using Cluster Administrator, connect to the cluster and select the SQL Server cluster.  Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.
28. How do you open a Cluster Administrator?
  • From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> 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?
  • In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.
The following Question and Answers were added recently. (on Dec 14th 2011)
30. What are the different ways you can create Databases in SQL Server?
  • T-SQL; Create Database command.
  • Using Management Studio
  • Restoring a database backup
  • Copy Database wizard
31. When setting Replication, can you have Distributor on SQL Server 2005, Publisher on SQL Server 2008?
  • No you cannot have a Distributor on a previous version than the Publisher.
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.
  • Yes it is possible to have various configurations in a Replication environment.
33. What is the difference between dropping a database and taking a database offline?
  • Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.
34. Which autogrowth database setting is good?
  • Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).
35. What are the different types of database compression introduced in SQL Server 2008?
  • Row compression and Page compression.
36. What are the different types of Upgrades that can be performed in SQL Server?
  • In-place upgrade and Side-by-Side Upgrade.
37. 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.
38. 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.
39. What are the operating modes in which Database Mirroring runs?
  • Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
40. What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?
  • 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.
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)