0

SQL Server Upgrades – Rules & Restrictions

Hello Friends, Today we will discuss about rules according to which Sql server upgrades are possible. These operations can be edition specific or version specific.

It is not possible to upgrade any edition, version without any rules or restrictions. There are some set of rules.
Let’s see how this works.

Edition Upgrades

Most of the time organization make decisions about edition of Sql server based on the requirements which it has at that point of time. It chooses the edition which is minimum to fulfill all the requirements. But later in time when organization grows, it needs to expand its business, data and Sql server edition also to get more features.

You can get more information about features which are available in different editions here.

However this is considered a wise decision to install lower edition based on the current set of requirements but sometime it has its downsides also. When organization choose to upgrade to higher edition then it may need to upgrade its infrastructure also to get the full benefits of all the features which higher edition offers. Due to this additional cost sometime organizations opt to install higher edition at the first step to avoid any additional cost in between.

You can upgrade all editions of SQL Server 2012 except for the Compact Edition to higher tier SQL Server editions. For example, you can upgrade Express, Web, and Workgroup editions to Standard, Enterprise, or Data Center editions. You can upgrade the Standard Edition to only the Enterprise or Data Center editions.

Edition Upgrade Matrix

  • Evaluation edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise
  • Developer edition can be upgraded to:
    • Web
    • Standard
    • Enterprise
  • Standard edition can be upgraded to:
    • Enterprise
  • Web edition can be upgraded to:
    • Standard
    • Enterprise
  • Express edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise

According to above matrix you can find out that is it possible for the edition upgradation you are looking for. Based on this you can actually make decision on which edition you should go for. You should choose the edition so that in future you should be able to upgrade easily which any major problems.

Version Upgrade

When it comes to upgrade Sql server version then it can come with two flavors.

  1. Either you want to upgrade only version not the edition. E.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 standard edition.
  2. Or you want to upgrade both e.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 enterprise edition.

There are again set of upgrade which are possible. Below is the list of those:

Version Upgrade Matrix

Upgrade from

Supported upgrade path

SQL Server 2008 SP4 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 SP4 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 SP4 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 SP4 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2008 R2 SP3 Datacenter

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 R2 SP3 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 R2 SP3 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2012 (11.x) SP2 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2012 (11.x) SP1 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2012 (11.x) SP2 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2012 (11.x) SP2 Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2014 (12.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2014 (12.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2016 (13.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2016 (13.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) release candidate*

SQL Server 2017 (14.x) Enterprise

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) Enterprise

 

So one can make decisions of which edition\version it should buy so that it could be upgraded easily to new version\edition.

0

Database Extensibility With Agile

Hi Friends, Today we are going to discuss about database extensibility and how this can be fit in business scenario for agile development.

What is Database Extensibility?

Database extensibility is the factor that how easy a database can adopt new changes without any major schema refactor or side effects.

If database schema is created in such a way that a small change in any table is causing you major changes in stored procedures, functions and client code to get data then this can be considered as a poor database design.

Database objects like tables, stored procedures, functions, views, triggers etc. should be written in such a way that it should be able to adopt new changes very easy with minimum or no code changes.

There are few guidelines to make database more extensible.

  • Tables should be normalized properly.
  • Generalization should be there to extend specific domain entities.
  • Data duplicity should not be there in multiple tables.
  • One table should hold specific domain entity requirement.
  • Do not use a table to hold non relevant data with various nulls in columns.

What is Agile Development Methodology?

Being developers we very well aware of waterfall method. Waterfall is a development methodology in which we have a major part of a project goes sequential. Following are the steps:

  1. We do requirement gathering of a major project or major part of any project.
  2. We prepare designs, get approvals.
  3. Code the entire solution in one go.
  4. Testing
  5. Control

These stages are sequential stages but the main problem is volume of work. However this methodology work fine but when it comes to larger projects then each stage takes significant amount of time and efforts. And when we achieve final implementation, till that time there could happen many business\requirement changes. These changes can create issues or again initiate a new waterfall process to tackle those changes.

In waterfall model, one has to wait till the completion of final code and product is visible.

To cater this issue, agile methodology came into picture. This is iterative approach having 15\21\30 days of sprints. Each sprint deliver a working code which can be demonstrated to client and any requirement changes can be incorporated at early stage of development.

This way one can save lots of rework and client\team can see work in progress and validate it at the same time against requirements.

Why we need database extensibility?

As we know that agile works through small iterations. As we cycle through iterations, an extensible database absorbs new business requirements with less refactoring.

If database design is poor then in further stages\iteration it will be messy to get everything fit into the database. So one should make decision at the earlier stages of designing and make database as much extensible.

Benefit of good schema

There are several benefits of good database design. Below are few.

Set Based Queries

SQL Server is designed to handle data in sets. SQL is a declarative language e.g. query describes the problem, and the Query Optimizer generates an execution plan to resolve the problem as a set.

Iterative T-SQL code is code that acts upon data one row at a time instead of as a set. The implementation generally happens via cursors. This forces the database engine to perform thousands of wasteful single-row operations, instead of handling the problem in one larger, more efficient set.

This is why set-based queries, based on an obvious physical schema, are so critical to database performance.

Indexing

A sound indexing strategy identifies a handful of queries that represent 90 percent of the workload. With judicious use of clustered indexes and covering indexes, solves the queries without expensive lookup operations.

An elegant physical schema, well-written set-based queries, and excellent indexing reduce transaction duration. And this implicitly improves concurrency and sets up the database for scalability.

Concurrency

A database with an excellent physical schema, well-written set-based queries, and the right set of indexes will have tight transactions hence perform well with multiple users.

When a poorly designed database displays symptoms of locking and blocking issues, transaction isolation level tuning only partially alleviates the problem. The sources of the concurrency issue are the long transactions and additional workload caused by the poor database schema. Concurrency tuning cannot overcome the deficiencies of a poor database design.

Scalability

The database component is the principle factor determining the overall monetary cost of the database. A well-designed database minimizes hardware costs. It simplifies data access code and maintenance jobs. It significantly lowers both the initial and the total cost of the database system.

Lower maintenance cost leads to scale systems easily.

You can read more about database design principals here.

We can see that a good database schema is the back bone of overall system. It can give countless direct\indirect benefits. While a poor designed database can create issues unexpectedly.

0

Database Design Principles

Hello friends. As we are in process of exploring good database architecture and concepts so it is very important to look into few Database Design Principles of a good database architecture. These database design principles are basis for any good database and should be kept in mind always while designing database systems.

Before going into principle part, let’s first discuss about what is information. Information is an valuable organizational asset and according to its value and scope, it must be organized, stored, secured, and should be readily available in a usable format for daily operations and analysis by individuals, groups, and processes, both today and in the future.

Everything is an information which is valuable to someone and can be used in productive or destructive ways. As there could be many dangerous impact of information if it is in wrong hands so it is very important to organize the information in secure way.

Looking into all above key attributes of information, let’s discuss now good information management system.

There are six main objectives which must be fulfilled effectively by a good database.

  1. Usability
  2. Extensibility
  3. Data Integrity
  4. Performance
  5. Availability
  6. Security

Let’s discuss a little in detail.

Usability

Any information which we are storing in any organization should be meaningful for that organization. If we are storing those factors which are actually not fit with organization’s requirement then this is just waste of resources.

Primary objective of any information system should be to meet organization requirements. Following are few points to consider while going to start an architecture.

  1. Properly get details about requirements.
  2. See how information can be fit with requirement.
  3. Trace requirement matrix to capture mapping of information architecture and requirements.
  4. Organize it simple.
  5. Decide upon format of data so that could be easily converted to meaningful representation.

Extensibility

As we know that everyday new business requirements come up and every day there is a need to change or enhance information system to capture new requirements. So information design should be extensible so that it can adopt new requirements without much efforts or without major breaking changes.

If your initial design is too much complex or unorganized then it may create trouble for you to adopt new things effectively.

Following are few points to consider when thinking of extensibility.

  • Normalization and correct handling of optional data.
  • Generalization of entities when designing the schema.
  • Data-driven designs that not only model the data but also enable the organization to store the behavioral patterns or flow which can be hooked up in different stages of information processing.
  • A well-defined abstraction layer that decouples the database from all client access, including client apps, middle tiers, ETL, and reports.
  • Extensibility is also closely related to simplicity. Complexity breeds complexity. A simple solution is easy to understand and adopt, and ultimately, easy to adjust later.

 

Data Integrity

Now at this point we understand that information is very much important for any organization. Based on the historic information, every organization makes different strategies, decisions for growth. One small mistake in data can lead to major issues with any organization’s key decision and hence a big risk for growth.

When we are designing a good information system then we must keep in mind about integrity, correctness of data. Our system should be smart enough to handle incorrect, missing data attributes and based on that it should either take corrective actions or straightaway reject the data. Incorrect data should not be present in system or at least should not exposed to individuals creating misunderstanding.

Data integrity can be of many types.

Entity Integrity

Involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.

Domain Integrity

It defines that data should be of correct type and we should handle optional data in correct way. We should apply Nullability to those attributes which are optional for organization. We can define proper data types for different attributes based on organization’s requirement so that correct format data should present in system.

Referential Integrity

This defines if any entity is dependent on another one then parent entity should be there in the system and should be uniquely identifiable. We can do this by implementing foreign keys.

Transactional Integrity

This defines that transaction should have its ACID properties. Any transaction should be atomic, consistent, durable and isolated. The quality of a database product is measured by its transactions’ adherence to the ACID properties:

Atomic — all or nothing

Consistent — the database begins and ends the transaction in a consistent state

Isolated — one transaction does not affect another transaction

Durable — once committed always committed 

User defined integrity

There are few business rules which we cannot validate just by primary keys, foreign keys etc. There has to be some mechanism so that we can validate complex rules for integrity. We can implement these rules in following ways:

  • Check Constraints
  • Triggers & Stored Procedures
  • Queries to identify incorrect data and handle in correct way.

 

Performance

As we know that information should be readily available as requested. Performance of the system should be up to the mark. As data in increasing day by day so at some time there will be impact on performance if database design is poor or we’ll not take any actions to improve performance.

Following could be few strategies which we can implement when there is need as data increases.

  • A well-designed schema with normalization and generalization
  • A sound indexing strategy, including careful selection of clustered and nonclustered
  • Tight, fast transactions that reduce locking and blocking
  • Partitioning, which is useful for advanced scalability

 

Availability

The availability of information refers to the information’s accessibility when required regarding uptime, locations, and the availability of the data for future analysis. Disaster recovery, redundancy, archiving, and network delivery all affect availability.

Following are the factors which impact availability:

  • Quality, redundant hardware
  • SQL Server’s high-availability features
  • Proper DBA procedures regarding data backup and backup storage
  • Disaster recovery planning

Security

For any organizational asset, the level of security must be secured depending on its value and sensitivity. Sometime organizations has suffered a lot because of data leaks which results in loss of faith and tends to business risk. So security is one of the most important aspect of good database design.

We can enhance Security by the following:

  • Physical security and restricted access of the data center
  • Defensively coding against SQL injection
  • Appropriate operating system security
  • Reducing the surface area of SQL Server to only those services and features required
  • Identifying and documenting ownership of the data
  • Granting access according to the principle of least privilege, which is the concept that users should have only the minimum access rights required to perform necessary functions within the database
  • Cryptography — data encryption of live databases, backups, and data warehouses
  • Metadata and data audit trails documenting the source and veracity of the data, including updates

Based on above principles, one should start designing databases and architectures.

0

Database Core Components & Basic Introduction

Hi Friends, Today we are going to discuss about Database Core Components\Tools of Sql server and purpose for those. It’s a very basic and introductory information which I am going to provide here. But we’ll discuss each component in details in upcoming sessions.

So let’s start.

Database Engine

The Database Engine component of SQL Server is the core service for storing, processing, and securing data. This is the component which actually execute queries and process the data. This component secure data by storing it in various data files.

By installing database engine, you can create new databases on that instance and store your data. We cal this as relational engine also. Sql is a descriptive language so it passes the query to the engine and then engine is responsible to handle all relational work.

Whenever we connect to any database using tools like management studio for queries, we actually connect to database engine. 

SQL Server Agent

This is the component which use to take care of all automated tasks and jobs. It maintains list of all Sql jobs, their tasks and alerts. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

Sql server agent is a window service which runs in background. We can configure this service to start on windows startup.

SQL Server Agent Components:

  • Jobs:job is a specified series of actions that SQL Server Agent performs. 
  • Schedules:schedule specifies when a job runs.
  • Alerts: An alert is an automatic response to a specific event. 
  • Operators: An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. 

SQL Server can notify operators of alerts through one or more of the following:

  • E-mail
  • Pager (through e-mail)
  • net send 

Database Mail

Database mail component is specifically for sending mails. There could be need of sending mails as alerts in case of any scheduled task failure. So one can configure SMTP and database mails, so that it can send automated alerts.

Not only automated alerts but one can send mails through T-Sql code also. The mail can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

MSDTC

MSDTC (aka Microsoft Distributed Transaction Control) is a software or we can say it’s a windows service which is used by SQL Server and other applications when these want to make a distributed transaction between more than one machine\servers. This uses dual phase commit protocol.

When one server starts a transaction and this transaction include few statements which needs to be executed on another server and we want to secure this complete batch by one transaction, then MSDTC comes into picture. It tracks all success\failure of all statements and commits the entire batch if all statements are succeeded.

Business Intelligence

Business intelligence (BI) is a package or set of tools that enable the management of data for the purpose of analysis, reporting, mining, and visualization.

SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (RS), and Analysis Services (AS).

SSIS

This is integration service which one can use for integration of data from various sources. It has a visual interface which we can use to design packages.

We can say SSIS as ETL i.e. Extract-Transform-Load tool. We can design package where it can fetch data from other source, file etc. and then perform the transformation according to requirement and then load to another system\file.

As it has visual interface so it becomes very easy to perform all these transformations\load rather than T-Sql. 

SSRS

Reporting Services (RS) for SQL Server 2012 is a full-featured, web-based, managed reporting solution. RS reports can be exported to PDF, Excel, or other formats with a single click and are easy to build and customize.

One can schedule these to pre-created and cached for users, e-mailed to users, or generated by user on-the-fly with parameters

There is new functionality in SSRS 2012 with the addition of Power View. This is a SharePoint integrated feature that provides for rich drag and drop visualization and data exploration. It is one of the hottest new features in SQL 2012.

SSAS

The Analysis Services service hosts two key components of the BI tool set: Online Analytical Processing (OLAP) hosts multidimensional databases where data is stored in cubes, whereas Data Mining provides methods to analyze datasets for nonobvious patterns in the data.

Building cubes in a multidimensional database provides a fast, pre-interpreted, flexible analysis environment. One can include robust calculations in a cube for later query and reporting. We can use these results to reporting or pivot table later.

One can use data mining to explore non trending patterns and specific patterns based on various algorithms. This can be very useful to perform data analysis so that business can get information about specific data patterns and make business strategies according to that analysis.

 

In addition to above components, there are few tools which are also very popular and very much used to worth discussing about.

Management Studio

This is a tool which gives you visual interface for servers, databases, tables, schema etc. One can connect to any database engine and query.

This is very useful tool which is used by almost all those who involves in database work either developer or DBA.

 

Configuration Manager

We can use this tool to start and stop any server, set the start-up options, and configure the connectivity. One can launch it from the Start menu or from Management Studio. It can show you all the services and servers running on a particular server.

It looks something like below image.

Profiler

Sql profiler is a visual tool which can track almost all events on any server. You can check queries, execution reports etc. One can use it for debugging\troubleshooting and performance purpose.

Profiler is being deprecated in favor of extended events.

Performance Monitor

When we install Sql server, it installs many performance counters. We can see these counters in Windows’s administrative tools. One can use these counters to check Sql server performance in terms of memory usage, I\O etc.

 

DB Engine Tuning Wizard

The Database Engine Tuning Adviser analyzes a batch of queries (from Profiler) and recommends index and partition modifications for performance. Database Engine Tuning Adviser examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

 

Command Line Utilities

Sql provides few command line utilities to perform specific tasks through command. Following are few examples.

SqlCmd:

One can run this command and provide query to execute on database. Database connection will be provided as parameters in this utility.

sqlcmd -d AdventureWorks2012 -q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

BCP:

The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. One can use BCP utility to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. 

Below is the sample to export query data into file.

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\BCP\People.txt -t, -c –T

Above is very basic information about major tools and components. In upcoming sessions we’ll discuss more details about each of these.