Always on Availability Groups is a solution which improves database availability and supports a failover environment for a discrete set of user databases.
It is introduced in SQL server 2012.
- With the release of Windows Server 2016, Windows Server Failover Clusters will no longer require that all nodes in a cluster reside in the same domain.
- You can now form a WSFC (windows server failover cluster) cluster from machines which are in workgroups. Because of this change, SQL Server 2016 is now able to deploy Always On Availability Groups in environments with:
- All nodes in a single domain
- Nodes in multiple domains with full trust
- Nodes in multiple domains with no trust
- Nodes in no domain at all
- Availability Modes:
- Asynchronous-commit mode: This availability mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances.
- Synchronous-commit mode. This availability mode emphasizes high availability and data protection over performance, at the cost of increased transaction latency. A given availability group can support up to three synchronous-commit availability replicas, including the current primary replica.
- Always On Availability Groups Supports up to nine availability replicas.
- Zero data loss protection in case of synchronous data flow.
- Failover of multiple databases.
- Active use of secondary replica for the purpose of backup and read only operation.
- It supports three major failover forms of availability group
- A listener is assigned to each availability group which is a serve to which client can connect in order to access a database in a primary or secondary replica of AAG.
- It supports automatic page repair in case of a page corruption.
Note: Always on another blog coming soon ……
Posted in Clustering, Database Administration
Tagged Always on, always on technology, benefits of always on, Clustering, clustering with always on, database always on, interview notes, interview questions on always on, mirroring, mirroring vs alwayson, questions on always on, sql server always on
Basic interview questions and answers for my fresher technical friends:
Q & A
- 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.
- Which command using Query Analyzer will give you the version of SQL server and operating system?
Select serverpropperty (‘productversion’),
Select serverproperty ( ‘productlevel’),
Select serverproperty (‘edition’).
3. Name the system databases?
- Resource (Hidden)
4. Name the recovery models in Database?
5. What does DBCC stands for?
DBCC stands for database consistency checker
- What is the fundamental unit of storage in SQL Server data files and what is its size?
A page with a size of 8kb
- How many different types of pages exist in SQL Server?
- Text/Image (LOB, ROW_OVERFLOW, XML)
- GAM (Global Allocation Map)
- SGAM (Shared Global Allocation Map)
- PFS (Page Free Space)
- IAM (Index Allocation Map)
- BCM (Bulk Change Map)
- DCM (Differential Change Map)
- What is SA login in SQL Server?
SA stands for System Administrator and it is the most powerful login in sql server.
- What are the authentication modes available in SQL Server?
- What is the default fill factor value?
Default fill factor value is 0 or 100
- What are the DCL commands?
DCL- Data control Language
SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQLstatements (also commonly referred to as a malicious payload) that control a web application’s database server. Such attack affects any website or web application.
An attacker can bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL injection can also be used to add, modify and delete records in a database.
In a 2012 study, it was observed that the average web application received 4 attack campaigns per month, and retailers received twice as many attacks as other industries.
A SQL injection needs two conditions to exists :
- A relational database that uses SQL
- A user controllable input which is directly used in an SQL query.
Subclasses of SQLi –
- Classic SQLi
- Blind or Inference SQLi
- Database management system-specific SQLi
- Compounded SQLi
Here user need to provide user name and password, if attacker provides ‘or 0=0’ as the username and password then the query will be like this.
Since the inputs provided by the attacker are valid in all circumstances, the query will return all records in the database.
And by this way an attacker will be able to view the sensitive information.
- Adopt an input validation technique where user input is checked against a set of rules.
- Users should have least privileges on the database.
- Don’t use ‘SA’ accounts for web applications.
- Need to have application specific database user accounts.
- Remove all stored procedures which are not in use.
In continuation of previous blog-
Server Core is a minimalistic Microsoft Windows server installation option.
You can install SQL Server on a Server Core installation of Windows Server 2008 R2 SP1 or Windows Server 2012. This option of windows server 2012 provides a minimal environment for running specific server roles. This helps to reduce maintenance and management requirements and the attack surface for those server roles. Starting with Windows Server 2012, it is possible to transition between Full Server and Server Core without reinstalling the operating system.
Server Core is designed to provide an environment that reduces:
- Servicing requirements
- Management requirements
- Attack surface
- Disk space usage
- Memory (RAM) usage
Server Core supports the following server roles on Windows Server 2012 and Windows Server 2012 R2:
- Active Directory Domain Services (AD DS)
- Active Directory Lightweight Directory Services (AD LDS)
- DHCP Server
- DNS Server
- File Services
- Print Services
- Streaming Media Services
- Web Server (IIS)
- Active Directory Certificate Services
- Windows Server Update Server (WSUS)
- Active Directory Rights Management Server
- Routing and Remote Access Server, including the following sub-roles:
- Remote Desktop Services Connection Broker
Configuration Level: configuration levels in order of increasing complexity for windows server installations.
Note: In newer Windows versions there are more granularity in configuration levels.
In continuation of previous blog:
A contained database includes all database settings and the metadata within itself which results no configuration dependencies on the instance of SQL Server. Therefore a contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.
We need to make changes on the server level followed by database level.
Server Level Settings:
Go to SSMS–> Right click on Server name–> Properties–>Advanced–> Containment–>choose True
Database level Settings:
Now to change the settings on the database which you want to be a contained database, we can do it for existing as well as with new database for the required server edition.
When a database is created the “Containment type” should be set to “partial” to make the database a contained database, as shown below.
Here we need to choose the partial option for containment type.
After that we have to create a user under the contained database and check the login for that user as below. Here user is created with name Mirza and we are checking by connecting it.
Here we have to mention the database which is contained and then connect it by using user.
Note: Initially I promise to provide only brief summary however few features are interesting and they can’t be bound in one line definition. This blog is such an example.
Looking forward your likes and response!
In continuation of previous blog:
- Pagination: This feature focuses on the result set of T-sql. It divides the result set into different parts to view easily. We can do pagination by using ‘OFFSET’ and ‘FETCH’ commands. We can pull only the required number of records instead of all.
There is an emp table and we are applying pagination on the same.
Here I have applied pagination for 0 to 5 records.
Few points to ponder:
- ORDER BY is mandatory to use OFFSET and FETCH clause
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
In continuation of previous blog..
A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. This sequence is generated in an order of ascending or descending at a defined interval. Sequence is not attached to a table.
A. Creating a sequence that increases by 1
create sequence dbo.empby1
start with 5
increment by 1
How to check sequence through t-sql:
select next value for dbo.empby1
B. Creating a sequence that decreases by 1
create sequence dbo.empneg1
start with 0
Increment by -1;
C. Creating a sequence using default values
create sequence dbo.mirzaseq ;
As promised to provide little glimpse of each feature from the definition point of view, I am ready to deliver each feature each week for your kind notice.
- Column store Indexes:
A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.
For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner.
They are the preferred data storage format for data warehousing and analytics workloads.
Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x.
Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload.
How to create index through SSMS:
Questions may be asked from the DBA prospective so I tried to collect few important features for SQL Server 2012,2014 and 2016 . Next blog will be published with explanation for each feature.
SQL Server 2012 Features:-
- Column store indexes
- Contained Database
- Sequencing Objects
- Error Handling
- User defined roles
- Windows server core support
SQL Server 2014 Features:-
- New In-Memory OLTP Engine
- Enhanced Windows Server 2012 Integration
- Enhancements to AlwaysOn Availability Groups
- Enhancements to Backups
- SQL Server Data tools for Business Intelligence
- Updateable Column store Indexes
SQL Server 2016 Features:-
- Always Encrypted
- Stretch Database
- Real Time Operational Analytics
- Poly Base into SQL Server
- Native JSON Support
- Enhancements to Always On
- Enhanced In-Memory OLTP
- Revamped SQL Server Data Tools
Note: A brief description will be published for each feature in coming post.
Please like or comment if it is beneficial for you.
REDMOND, Wash., and MOUNTAIN VIEW, Calif. — June 13, 2016 — Microsoft Corp. (Nasdaq: MSFT) and LinkedIn Corporation (NYSE: LNKD) announced they have entered into a definitive agreement under which Microsoft will acquire LinkedIn for $196 per share in an all-cash transaction valued at $26.2 billion, inclusive of LinkedIn’s net cash. LinkedIn will retain its distinct brand, culture and independence. Jeff Weiner will remain CEO of LinkedIn, reporting to Satya Nadella, CEO of Microsoft.
LinkedIn is the world’s largest and most valuable professional network and continues to build a strong and growing business.
Nadella’s Statement about LinkedIn:
“The LinkedIn team has grown a fantastic business centered on connecting the world’s professionals. Together we can accelerate the growth of LinkedIn, as well as Microsoft Office 365 and Dynamics as we seek to empower every person and organization on the planet.”
Note: Please refer the below link from Microsoft to get the complete news.
Kindly share if you like it.