SQL Server 2014 top New Features

let’s talk about the Top New Features of SQL Server 2014 SQL14

  1. In-Memory across Workloads Built-in
  2. Scalability Enhancement and Availability
  3. Power BI For Office 365
  4. Backup Encryption
  5. Microsoft Azure Integration

In-Memory across Workloads Built-in

It’s allows you to create memory-optimized database objects that enable more efficient data access **Remember select only Highly Utilized tables to be In-Memory**

that’s will improve performance by as much as 10 times, and improves scalability by 2-5 times.Per

It does this be minimizing the latency it takes to retrieve data.

This feature will be great for applications that need performance, have relatively short transactions, have a high degree of concurrent transactions with many connectionsIn-Memory

Scalability Enhancement and Availability

The number of logical processors has been increased to 640 and memory up to 4 TBs.

If SQL Server running in a virtual environment it’s up to 64 virtual processors and 1TB of memory

If you have a clustered SQL Server environment it’s will support up to 64 different nodes.

ABOUT High Availability the number of secondary Replica maximum from four to eight.

AG

Power BI For Office 365

It’s a cloud-based business intelligence (BI) solution which provides data navigation and visualization capabilities

Power BI for Office 365 includes Power Query , Power Map , Powerpivot, and Power ViewBI

Backup Encryption

It’s the ability to encrypt database backups. SQL Server 2014 supports several encryption algorithms; including Advanced Encryption Standard You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.0572.1

More info about Backup Encryption https://helmymo7amed.wordpress.com/2014/07/14/sql-server-2014-backup/

 Microsoft Azure Integration

It’s the Ability to Automatic/Manual Backup to Azure Environment and Working with Azure Secondary Replica and storage. we can set an Availability Group resides in Azure when the Primary Replica Failure happens we can switch Manual but we can still working and Report to/from databaseAzure

Advertisements

SQL Server 2014 Backup

SQL Server 2014 Database Backup Encryption is away of Database Backup using Encryption Key and Advanced Algorithms ” AES 128, AES 192, AES 256, and Triple DES ” and it’s integrated with the Product.

Configure Backup

1- Create Database Master Key for Master Database.

USE Master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'COmpl£xP@$$W0rd';
GO

2- Create a Backup Certificate in SQL Server 2014.

USE Master
GO
CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'SQL Server 2014 Backup Encryption Certificate';
GO

Screenshot (78) 

3- Backup Steps 

  1. Access Database using Management Studio
  2. Select Database
  3. Tasks
  4. Back UpScreenshot (79)
  5. in General Setting we can choose Back Up to Disk OR URL
    • Disk is a local storage in a traditional way.
    • URL is a new feature which enable to backup data on Microsoft Azure  using Credentials and Azure container.Screenshot (80)
  6. in Backup Options we can choose the Algorithms Screenshot (81)
  7. Click OK

 

 

SQL Server 2014 Failover Cluster

Prerequisites

  1. 1 VM For DC Server 
  2. 1 VM For iSCSI Target Server
  3. 2 VM For SQL Server Node 1 and SQL Server Node 2

VMs

 Architecture

Storage iSCSI Target Server Installation

  • Open Server Manager
  • Add roles and featuresScreenshot (27)
  • Role-based or feature-based installation and click Next
  • Server Roles and then select iSCSI Target Server to enable iSCSI target server role
  • Next
  • Features: Click Next
  • Confirmation: Click Installation
  • RestartScreenshot (28)

Configure iSCSI Server 

  • Open Server Manager and click File and Storage Services and then click iSCSI
  • Select New iSCSI Virtual DiskScreenshot (68)
  • iSCSI Virtual Disk Location Select the Volume Drive to create the virtual disk
  • Type the size of the virtual disk and click NextScreenshot (71)
  • Select New iSCSI target and click NextScreenshot (70)
  • Type the name of the new Target Server and click NextScreenshot (72)
  • Click Add to add an initiator and click Next
  • Add Node 1 and Node 2 as Initiators.Screenshot (74)
  • Enable authentication: Click Next
  • Confirmation: Click Create
  • Click CloseScreenshot (76)

Node 1 and Node 2 Initiator Configuration 

  • Open iSCSI Initiator and Click DiscoveryScreenshot (34) 
  • Click Discovery Portal and type the IP address of the iSCSI Target server and click OKScreenshot (35)
  • Click Targets tab and Select the target within the Discovered targets and click Connect
  • Click OK
  • Open Server Manager and click File and Storage Services and then click DisksScreenshot (31)
  • The new disk (Quorum) should be presented as Offline
  • Right click on the disk and select Bring Online and then Click Yes Screenshot (32)
  • Right click on the new disk and select New Volume
  • Select Server and Disk
  • Select the size for the new disk and then click Next
  • Select drive letter and ten Click Next
  • Type the label of the volume (Quorum Disk)
  • Click Create
  • Close

 Setup Windows Server 2012 Failover Cluster

Node 1 and Node 2  Install Failover Clustering

  • Open Server Manager and 
  • Click Add roles and featuresScreenshot (37) 
  • Click Next
  • Select the server on which to install roles and features Select ” Select The 2 Nodes for Installation ” and click NextScreenshot (38)
  • Features: Select  Failover Clustering Click Next
  • Confirmation: Click Installation
  • Restart in necessary

Node 1 and Node 2 Validate and Create Windows Cluster

  • Open Failover Cluster Manager
  • Click Validate Configuration
  • Click NextScreenshot (41) 
  • Select Servers or a Cluster Add the node names Node 1 and Node 2 and click NextScreenshot (43) 
  • Testing Options: Select Run all tests and click Next
  • Confirmation Click NextScreenshot (46)
  • Validating Create the cluster now using the validated nodes is checked and click Finish
  • Click NextScreenshot (48) 
  • Access Point for Administering the Cluster Type the cluster name SQLNodeClu.
  • Type the IP address from the public network.Screenshot (49) 
  • Add all eligible storage to the cluster Click Next
  • FinishScreenshot (50)
  • The New Server failover cluster installed on Node 1 and Node 2 .

Screenshot (57)

Installing SQL Server 2014  as a Clustered Instance on Node 1

Begin SQL Server 2014 installation on the first cluster node.

  • Choose the Installation tab and choose the New SQLServer failover cluster installation

Screenshot (4)

  • Accept the License Terms

Screenshot (6)

  • Failover Cluster rules will be installed. It is essential that this step completes without errors.

Screenshot (8)

  • Choose the SQLServer Feature Installation

Screenshot (9)

  • Select the features.

Screenshot (10)

  • Provide a Network Name for your SQL instance.

Screenshot (12)

  • Specify a name for the SQL Server cluster resource group of proceed with the default.
  •  Proceed with the default Cluster Disk selected

Screenshot (13)Screenshot (15)

  • Choose both the IPv4 and IPv6 networks if available.

Screenshot (16)

  • Configure your SQL Server Agent and Database Engine accounts
  • Specify your SQL Server administrators and choose your authentication mode.

Screenshot (17)

  • Select the Data Directories tab. This allows you to customize the Cluster Shared Volumes paths where you want to store the files corresponding to your SQL Database.

Screenshot (18)

  • Proceed with the final SQL Server installation.

Screenshot (56)     Now add the other cluster nodes to the Failover Clustering Node2

  • choose the Add node to a SQL Server failover cluster option.

Screenshot (22)

  • Accept the Terms.

Screenshot (25)

  •  The Rules check occur and click Next

Screenshot (24)

  • By default Instance Name…, and click Next

1325.27

  • Set IPV4 from the same Subnet Mask and click Next

5164.28

  • Set the Admin Account and specify Password

5468.29

  • Click Install

8053.30

  • Finally the Node Added to the Sql Failover Cluster.

4520.31


Now

  • Open Failover Windows Cluster
  • SQL Server Running
  • 2 Online Nodes

Screenshot (58)

SQL Server Communication Protocolas

SQL Server,,clients Communicate with each other with…. Protocols%20for%20MSSQLServer

1-Shared Memory: A simple protocol using no configuration which working on the same environment ” client which is on the same physical machine as SQL Server  it’s provide a fast communication so it’s no useful for more (DBS) but it’s just for Troubleshooting.

2-TCP/IP: A Connection-Oriented Protocol that’s systems agree both connection with each other, TCP use different applications  share it through Ports, when A services like SQL Server come to run it must use a specific port to access through it .. For a default SQL Server instance, the default port is 1433, the main protocol which clients communicate with SQL Server. Client and Server use Tabular Data Stream[TDS] which sit on the top of TCP/IP, it communicate with all networks and pass(DBS), it also include A Routing ,Traffic and Security system .. It’s configured by default TCP/IP is a good choice for network protocol if your application does not require SQL Server’s integrated security.

3-UDP: unlike TCP, is not connection oriented. Client can send a UDP communications to anyone it wants. There’s nothing in place to negotiate a communications connection, it’s great for short communications that doesn’t necessarily need an acknowledgement, it uses ports just like TCP. The SQL Browser or the SQL Server Listener Service uses UDP. TCP UDP

4-Named Pipes: It’s for a local Area Network [LAN] its pass information from one to other but in the same network, it uses a part of memory of one process to store/pass the information, it means the output of one process becomes the input for another process. Named Pipes is an excellent choice for network protocol if your application intends to use SQL Server’s integrated security and does not need other protocols for remote connections.

5-Virtual Interface Adapter VIA: it’s work with VIA hardware, configuration details are available from your hardware vendor.

⇔ TCP/IP is the preferred protocol in a slow LAN, WAN or dial up network.

 Named Pipes is more considerable in fast local area networks.

 Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It’s about a transport protocol and a port number


Database Mirroring Endpoint

To make Database always on as a way of High-Availability we have to set an Endpoint to ensure the connection between the servers instances, it use the TCP protocol to send and receive messages between the server instances and This database mirroring endpoint use a Unique port number to establish the communication, The port number uniquely identifies a specific server instance. This figure show how two server instances on the same server are uniquely identified. Server network addresses of a default instance  SQL server instance by default not have an Endpoint it must be create manual and configure it.

 The DBA is the responsible for opening ports through firewall

SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = ‘DATABASE_MIRRORING’


Replication

Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.) Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another replication can use:-

  1. FTP (TCP port 21)
  2. sync over HTTP (TCP port 80)
  3. File Sharing.

♦ File sharing uses UDP port 137 and 138, and TCP port 139 if it using Net-BIOS. after Windows 2003, File Sharing uses TCP port 445.

♦ For sync over HTTP, replication uses the IIS endpoint (configurable ports it’s port 80 by default)

♦ The IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance. During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher.


Log shipping Considerations over the Firewall

While Log shipping uses SMB communication to copy the log files from the Network Share to the local destination folder and hence in order to configure over Firewall

♦ NetBIOS over TCP/IP is enabled

  1. TCP ports 137,139
  2. UDP ports 137,138

♦ Direct Hosting of SMB over TCP/IP

  1. TCP port 445

BTW,,

The following ports must remain open if going through a firewall:-

  1. Port 1433 must remain open for SQL to communicate back and forth.
  2. Port 135 required for SQL Agent to copy backup files to the shared folder on the standby server.
  3. Ports 137, 138, and 139 or 445 required to use UNC shares.

Log Shipping SQL Server 2012

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

8357.LogShipping.jpg-650x0

Benefits 

  • Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
  • Supports limited read-only access to secondary databases (during the interval between restore jobs).
  • Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  1. Copy the transaction log file to the secondary server instance.
  1. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

You can use a secondary database for reporting purposes.

In addition, you can configure alerts for your log shipping configuration.

Configure Log Shipping 

*Prerequisites

  • The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
  • Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you could create the \\primaryserver\tlogs share of that directory.1-Share backup folder

*Permissions 

The log-shipping stored procedures require membership in the sysadmin fixed server role.

configuration

-Right click the database you want to use as your primary database in the log shipping      configuration, and then click Properties.
-Under Select a page, click Transaction Log Shipping.
-Select the Enable this as a primary database in a log shipping configuration check box.
-Under Transaction log backups, click Backup Settings2-Bakup settings

-In the Network path to the backup folder box, type the network path to the share you created for    the transaction log backup  folder.

-Configure the Delete files older than and Alert if no backup occurs within parameters.

-Click OK.4-Network Path

-Under Secondary server instances and databases, click Add.3-Add

-Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.

5-Secondary server instance connect

-In the Secondary Database box, choose a database from the list or type the name of the database  you want to create.

-On the Initialize Secondary database tab, choose the option that you want to use to initialize the  secondary database.

-On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into -which the transaction logs  backups should be copied. This folder is often located on the secondary  server.6-secondary database destination path

-On the Restore tab, under Database state when restoring backups, choose the No recovery  mode or Standby mode option.

-Click OK.

Success7-Success

-once you open the secondary server8-Sql2 connect 

9-Secondary server final mirror

🙂

SQL Server Full Database Backups

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Benefits 

Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss.

With valid backups of a database, you can recover your data from many failures, such as:

  • Media failure
  • User errors, for example, dropping a table by mistake.
  • Hardware failures, for example, a damaged disk drive or permanent loss of a server.
  • Natural disasters.

Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Always On Availability Groups or database mirroring, and archiving.

Backup Types

differential backup : A data backup that is based on the latest full backup of a complete or partial database or a set of data files or file groups (the differential base) and that contains only the data that has changed since that base.

log backup : A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)

full backup : A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

 **Place the database and backups on separate devices. Otherwise, if the device containing the database fails, your backups will be unavailable. Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.

Create a Full Database Backup 

-After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
-Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.
-Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears
Backup-SQL-Server-Database-Using-SSMS-In the Database list box, verify the database name. You can optionally select a different database from the list.
In the Backup type list box, select Full.
Back-up-Database-Using-SQL-Server-Management-Studio-press OK

Database Backups Under the simple Recovery model

Under the simple recovery model, after each backup, the database is exposed to potential work loss if a disaster were to occur. The work-loss exposure increases with each update until the next backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts. Work-loss exposure increases over time between backups. The following illustration shows the work-loss exposure for a backup strategy that uses only full database backups.

Shows work-loss exposure between database backups

 

Database Backups Under the Full Recovery model

For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction log backups are also required. The following illustration shows the least complex backup strategy that is possible under the full recovery model.

 

Series of full database backups and log backups

>> http://msdn.microsoft.com/en-us/library/ms186289.aspx