Microsoft SQL Server 2012 - Log Shipping Explained

This video is intended to provide a high-level overview of Log Shipping within Microsoft SQL Server 2012 and a brief demo of a simple implementation.

 

Windows Cluster & AlwaysOn Availability

This video describes how to configure a Windows Cluster on Windows Server 2008 R2 to use with the AlwaysOn Availability Group feature in SQL Server 2012.

 

This video describes how to configure AlwaysOn Availability Groups in SQL Server 2012.

Transactional Replication

Transactional replication is a feature of SQL Server that replicates committed changes to the publisher database to one or more subscriber databases. Transactional replication is useful when you need data that is up to date in different locations. In this video Scott Golightly shows how to set up transactional replication. First the publisher is configured, then the distributor, next publications with articles are created, and finally a subscriber is created. The video shows making changes to the publisher database and having those changes appear at the subscriber.

Add new stored procedure in transactional replication by TSQL script in SQL Server

This script explains how we can insert a new stored procedure by script in an already established transactional replication.

USE AdventureWorks2012

GO

DECLARE @ProcName VARCHAR(25)

DECLARE @SchemaName VARCHAR(25)

DECLARE @PublisherName VARCHAR(25)

SET @ProcName='buildProcEmployeeDetails'

SET @SchemaName='HumanResources'

SET @PublisherName='AdventureWorksPublisher'

EXEC sp_addarticle

@publication = @PublisherName,

@article = @ProcName,

@source_owner = @SchemaName,

@source_object = @ProcName,

@type = N'proc schema only',

@description = N'',

@creation_script = N'',

@pre_creation_cmd = N'drop',

@schema_option = 0x0000000008000001,

@destination_table = @ProcName,

@destination_owner = @SchemaName,

@status = 16,

@force_invalidate_snapshot=1

GO

Setting up Database Mirroring using TSQL

Database mirroring is a feature of SQL Server that allows an administrator to create a copy of the database that is always up to date. If the principal server fails or needs to be taken offline for maintenance the mirror server can be quickly set up to take on the workload. Mirroring provides high availability for your databases

video demonstrates setting up Database Mirroring using TSQL

* also shows a manual failover

To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness

Login Auditing

This topic describes how to configure login auditing in SQL Server 2014 to monitor SQL Server Database Engine login activity. Login auditing can be configured to write to the error log on the following events.

• Failed logins
  
• Successful logins
  
• Both failed and successful logins
  

You must restart SQL Server before this option will take effect.

Using SQL Server Management Studio

To configure login auditing

1. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine with Object Explorer.
   
2. In Object Explorer, right-click the server name, and then click Properties.
   
3. On the Security page, under Login auditing, click the desired option and close the Server Properties page.
   
4. In Object Explorer, right-click the server name, and then click Restart.

Database Level Auditing in SQL Server 2008 R2

In this video, you will learn how to setup database level auditing in SQL Server 2008 R2

Common Interview Questions

Common SQL Server Interview Questions

Name 4 types of table in SQL Server

Regular user tables ([dbo].[TestTable]), Temporary Tables (#Temp_TestTable) , Global Temporary Tables (##GTemp_TestTable), Table Variables (@VarTestTable)

Difference between #temp tables and @table variables in sql server?

Temp tables are defined, visible throughout the level that created them across batches.
Table variables are declared, only visible to the batch that declared them.
Table variables do not permit the creation of indexes after it is initially declared, temp tables do.
SQL Server maintains distributed statistics for temporary tables but not table variables

Name the 3 recovery models and give a brief description?

The models are full, simple and bulk logged

The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.  When the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table. With "Simple" there are No log backups. The "Bulk Logged" model permits high-performance bulk copy operations. Reduces log space usage by using minimal logging for most bulk operations. With "Bulk Logged" you can recover to the end of any backup, Point-in-time recovery is not supported.

What is the basic difference between a clustered and a non-clustered index? 

Cluster index sorts the data with respect to the column on which it is applied, where as a non-clustered index do not, it is just a logical sorting. Thus we can have only single clustered index and multiple non-clustered indexes on a table. Primary index on a table is clustered index. By default CREATE INDEX statement creates a non-clustered index

What is a clustered index? How many can a table have? How do they affect the table?

A clustered index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. With a clustered index the rows are stored physically on the disk in the same order as the index. A table can only have one clustered index. Writing to a table with a clustered index can be slower, if there is a need to rearrange the data. There can be a performance gain for large INSERTS when indexes are disabled. 

Name two types of functions?

There are table valued functions and scalar functions

What is a CTE and how is it defined?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

What is the difference between an inner and outer join?

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate). Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

What is the difference between WHERE clause and HAVING clause?                                                                          

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

What is the default listening port of SQL Server?

 1433 for TCP/IP and 1434 for USD connection. This info can be found in SQL Server Configuration Manager

What is a linked Server?

You can configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. I.e. another db somewhere on the network

Name two types of Authentication?

SQL Server supports two authentication modes, Windows authentication mode and mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server. 

What is lock escalation in sql server?

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server. Through the ALTER TABLE statement and the property LOCK_ESCALATION there are 3 different options available:

• TABLE
• AUTO
• DISABLE

What are credentials/Proxy?
  
Login: A login is any principal that is granted access to a SQL Server instance.  The access can be granted to domain users, domain group, or SQL Server authenticated accounts. 
Credential: A credential provides a mechanism to store login information for a domain account within SQL Server.  The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server. 
Proxy: A proxy is used by SQL Server Agent to map credentials to SQL Server Agent subsystems (i.e. PowerShell or SSIS).

Name 3 types of Replication?

Transactional Replication, Merge Replication and Snapshot Replication

 What is a CLR and when is it used?

Common Language Runtime is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

What tools could you use for performance tuning or query optimisation?

SQL Server Profiler, Trace, Execution Plans, Database Engine Tuning Advisor, DMV's

 What is an index hint in a query - describe the pro's and con's?

it’s when you specify the “with” &  “name of an index” in a select statement after the tablename as demonstrated below

1. SELECT PER.FirstName,
2. PER.LastName,
3. EMP.LoginID
4. FROM HumanResources.Employee EMP WITH (INDEX (PK_Employee_BusinessEntityID))
5. INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID;

The pros are that you might get better performance – for now. But what happens when more data is added to the table, and statistics change? What happens if you update SQL Server, and the query optimizer changes? Eventually, the disadvantages maybe that the index you’re using may not be the best one for the job – but SQL Server is going to continue to use it anyway

what does an execution plan describe?

Execution plans show you what's going on behind the scenes in SQL Server

Name two types of containers in SSIS?

There is a sequence container and also a foreach container

What does a configuration file for SSIS do?

Package configurations are useful when you want to deploy packages to multiple servers, when you move your packages from a development to production environment, or in any situation in which you want to provide property values to a package at runtime.

How can you control flow in SSIS?

Precedence constraints, i.e. on success or failure do something

What are the advantages of a Stored Procedure?

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

 What does Update statistics do?

sp_updatestats executes UPDATE STATISTICS, by specifying the ALL keyword, on all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables

what forums do you refer to when you need assistance?

books on line, various sql server forums on line i.e. www.sqlservercentral.com/Forums/ www.brentozar.com www.stackoverflow.com etc.

can a view have an index - please explain in further detail?

Yes, creating a unique clustered index on a view changes it to an indexed view. The clustered index is stored in SQL Server and updated like any other clustered index, providing SQL Server with another place to look to potentially optimize a query utilizing the indexed view.

what is the default size of a varchar datatype?

In management studio, when creating a table the default length is 50.
If you declare a variable of type varchar without specifying a length, the default length is 1
If you cast to varchar without specifying a length, the default length is 30.

what is the default size of a database file?

Data: initial size 3MB; Autogrowth: by 1MB, unrestricted growth. Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth

 list three types of isolation levels?

 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE

what is wrong with a cursor?

It means row by row processing, this can be slow as opposed to set based operations but can in some specific situations outperform set based operations.

what are table/query hints and how are they used?

Table hints override the default behaviour of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

what happens when on delete cascade is used?

You can use the on DELETE CASCADE option when creating foreign key constraints, it helps keep the referential integrity of your database intact

what is a denormalized database?

 In a relational database, denormalization is an approach to speeding up read performance (data retrieval) in which the administrator selectively adds back specific instances of redundant data after the data structure has been normalized. A denormalized database should not be confused with a database that has never been normalized
 
when would you not want tables in third normal form?

Denormalization may involve recombining separate tables or creating duplicate data within tables to reduce the number of tables that need to be joined to retrieve the requested data, which results in less I/O and CPU time

Name two other ways of connecting to SQL SERVER outside SSMS?

- SMO via powershell scripts etc when server is too busy to render the tables 

- SQLCMD -b -E -S %Server% -d %DB% -i%workdir%\%%x >> %curDir%\Deploymentlog.txt

 Tools for monitoring SQL server outside of SSMS?

Resource monitor in Task Manager to monitor IO etc go to View in toolbar and select columns
Fire up SQLCMD from the command line when ssms is too busy to render the interface. i.e. memory is at its full capacity

How to investigate & resolve locking

open the activity monitor from SSMS after right clicking on the instance 

alternatively , EXEC sp_who2

A list of processes will be displayed, and any processes which are currently in a blocked state will display the SPID of the processes blocking them in the ‘BlkBy’ column.

Also by using the DMV's
SELECT S.login_name,
               S.host_name,
               S.program_name,
               WT.session_id,
               WT.wait_duration,
               WT.wait_type,
               WT.blocking_session_id,
                WT.resource_description,
FROM sys.dm_os_waiting_tasks AS WT
INNER JOIN sys.dm_exec_sessions AS S ON WT.session_id - S.session_id
WHERE s.is_user_process = 1;

//filters out system sessions returns sessions that are currently waiting on something

What is the command DBCC and how is it used?

The Database Console Commands (DBCC) are a series of statements in Transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database. These commands are also used to fix existing issues. They are also used for administration and file management. E.g. DBCC CHECKALLOC, DBCC CHECKDB, DBCC CHECKCATALOG, DBCC CHECKFILEGROUP, DBCC CHECKTABLE

SQL Server offers two ways of running dynamic SQL what are they & why could they cause a security concern?

Using EXEC or Using sp_executesql . Both statements allow for potential SQL injection

What is the Difference between Truncate and Delete in SQL Server?

TRUNCATE is a faster operation to perform over DELETE. TRUNCATE will reset any identity columns to the default seed value. TRUNCATE operations cannot be rolled back. You can have a WHERE clause with DELETE but not TRUNCATE.

What is best practise for configuring Tempdb in SQL Server

Place tempdb on a separate disk to user databases, Place tempdb on the fastest IO subsystem possible, Create multiple data files for tempdb i.e. Allocate 1 data file per physical or virtual CPU core. Keep tempdb data files equally sized and have autogrow increments configured equally across data files. Stripe tempdb data files across SAN or RAID array where possible. Manually manage the growth of all data and log files i.e. its best to set it to the size of its disk as database activity halts when the database experiences AutoGrow events so it can cause unnecessary performance overheads

What syntax does an Error handling block of code in SQL Server contain? And what helpful error information can be referenced in the last block

BEGIN TRY, END TRY, BEGIN CATCH, END CATCH. The error information that can be referenced is ERROR_NUMBER() or ERROR_LINE() or ERROR_MESSAGE() or ERROR_SEVERITY() or ERROR_STATE();

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index. An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records - time taken is only proportional to the number of matching records. In general an index seek is preferable to an index scan (when the number of matching records is proportionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the total number of records in your table. Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate

Have you any experience of Disaster Recovery in SQL Server. Please explain what happened & what you did to recover the database?

An example might be when a specific database transaction log grew to the maximum capacity of the disk causing subsequent transactions to hang – I temporarily changed the recovery model to simple and shrunk the transaction log as a short term solution but requested additional disk space as a long term solution