SQL server Upgrade

 

When you upgrade to a new version of SQL Server, there are some critical things you should do to help avoid any surprise SQL Server performance issues. I mean new version  of SQL Server is 2016,2017 or 2019, Because Microsoft  closed its support up to some extent to older version

Why need the Requirement to upgrade the Version?

Basically it is because Microsoft ended Its support of some versions.

There are two type of Support

1- MainStream Support

2- Extended Support

SQL Server Versions	MainStream Support	Extended Support
SQL Server 2005	April 12, 2011	April 12, 2016
SQL Server 2008 or R2	July 8, 2014	July 9,2019
SQL Server 2012	July 11, 2017	July 12, 2022
SQL Server 2014	July 9, 2019	July 9, 2024
SQL Server 2016	July 13, 2021	July 14, 2026
SQL Server 2017	Oct 11, 2022	Oct 12, 2027
SQL Server 2019	July 1, 2025	Aug 1, 2030

For Upgrading any Version there are some preparations which need to be taken care.

1- Find the Upgrade Blockers. 

2- Clear Understanding of Version and Editions.

3- Clear Understanding of Features introduced and obsolete

3- Make a List of objects which use the features which  are obsolete from the newer version and currently in use.

4- Clear Hardware Requirement for newer Versions. 

For this Activity i am upgrading the Server from 2012 to 2017.

Process to Upgrade the SQL Server Version.

There are 3 ways to upgrade

1. - In-Place Upgrade

2. - Side-by-Side Upgrade

3. - Rolling Upgrade

Steps for an In-Place Upgrade 

In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:

1. Verify that backups exist for all databases (user and system). 

2. Review the list of requirements for SQL server 2017 and install whatever is needed.

3. Install SQL Server 2017 

4. Restore the Backup

Side-by-Side Upgrade

• 1- Backup all the database

• 2-Script out any and all necessary system objects.

            3-Review the list of requirements for SQL server 2017 and install whatever is needed.

• 4- Run the script to create the Object

• 5-Select database(s) to migrate and it take offline. 

• 6-Migrate database to new instance. Repeat for each database.

•  

 

 

 

Rolling Upgrade

1- Select any High-Availability 

2- Install new Server instance 2017 and make it  as a Secondary node

3- Fail the Primary Node now New Instance will be active and will be Primary Node

 

Checklist need to Perform Before Planning to Upgrade about version of Server

1-Checking of Deprecated Features  in new version

SELECT * FROM sys.dm_os_performance_counters   WHERE object_name  = 'MSSQL$JFT:Deprecated Features   

2- Get the List top indexes which are in use maximum times in the current version.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name

      ,IX.name                     AS Index_Name

      ,IX.type_desc                 AS Index_Type

      ,SUM(PS.[used_page_count]) * 8     IndexSizeKB

      ,IXUS.user_seeks AS             NumOfSeeks

      ,IXUS.user_scans AS             NumOfScans

      ,IXUS.user_lookups AS             NumOfLookups

      ,IXUS.user_updates AS             NumOfUpdates

      ,IXUS.last_user_seek AS         LastSeek

      ,IXUS.last_user_scan AS         LastScan

      ,IXUS.last_user_lookup AS         LastLookup

      ,IXUS.last_user_update AS         LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS 

ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS 

on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1

GROUP BY 

OBJECT_NAME(IX.OBJECT_ID) ,

IX.name ,

IX.type_desc ,

IXUS.user_seeks ,

IXUS.user_scans ,

IXUS.user_lookups,

IXUS.user_updates ,

IXUS.last_user_seek ,

IXUS.last_user_scan ,

IXUS.last_user_lookup ,

IXUS.last_user_update

3- Current Used Space at current instance and Available Space in the new instance.

4- Current OS Hardware Configuration.

5- Scripting of all user defined Objects.

 

The INSERT statement conflicted with the FOREIGN KEY constraint Error Type - 547

We are going to resolve the following error-The INSERT statement conflicted with the FOREIGN KEY constraint

Error Description for The INSERT statement conflicted with the FOREIGN KEY constraint

Msg 547, Level 16, State 0, Procedure spMVC_Dizsweb_QAScore_InsertUpdate, Line 103 [Batch Start Line 0]

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DizswebQAScoreCardAnswer_DizswebQAScoreCardSubCategoryID".

The conflict occurred in database "Dizsweb_Test",

                                        table "dbo.DizswebQAScoreCardSubCategory",

                                        column 'DizswebQAScoreCardSubCategoryID'.

To resolve this issue Please follow the following steps

STEP 1:-

    Check the Foreign Key Constraints with the Help of  following Query

    sp_help  Object_name

Step 2:-

   Try to get the default value in foreign key

   Case1- May be it will NULL and you are passing other than NULL

   Case2- May be default is 0 are passing NULL

   Case3- Value passing may be not exists in the table

   Case4- Id removed from the table

Step 3-

  Change the default value to the value which u r  passing.

For All types of SQL Server Error Click Here

Indexes in Python

Indexing is a standard feature of most programming languages. Java, Python, C#, C++, and C all have syntax like E[i] whereby an item with index i can be reference in E (which might be a character string, an array of numbers etc). 

Python goes further beyond, it is possible to refer to chunks  of a sequence instead of indexing items one at a time. This feature in python is called slicing a sequence. Other techniques, like split and join, make it easy to take apart strings and assemble lists of strings into a new strings . In the same way that hand tools allow people to cut, shape, reshape, and put together wood into furniture, slicing, split, and join operations form the “carpentry” of Python’s text processing tasks. Even more powerful methods, notabe regular expressions, could be likened to the power tools of text and string processing. This post only covers the basic techniques, leaving regular expressions and other more advance libraries of software as later topics to explore. Working with slices particularly in Python’s syntax for slices, takes some getting used to. With regular practice, it becomes easy to write expressions that take apart sequences in creative ways and use operators to assemble slices into a new sequences. For strings slicing is only the beginning. String methods provide ways to search, replace, trim and form new strings from sequences of strings.

Following three methods are used to get the data in python via indexing
  1    loc() Only for lables
  2    iloc()  only for integer
  3    ix()  for both label and and integer.

Data Science Sample example for Beginner

Data Science Complete  Example with Library Pandas and Matplotlib :-

Learning of data Science is very easy for developer  and IT backgroud people beacuse they knows about programming mathodolgy and syntax in various other language. As java have its own set of rules for execute the program in the same way python have its own.

In this post i will explain about the code regarding to plot the data of company for april 1st week sales graph. To demonstrate that i have designed my own data set and save that data in CSV File.

Basic requirement to run this program and code are following

1- Python

2- Juypter Notebook

3- pandas 

4- matplotlib

5- dataset (In my case it is CSV File)

Python 

 I have installed Python 3.7. If you are new please download that first.

Note:- Download the python from anaconda site and download distribution version, Because with distribution version all required library will be installed, User don't need to download that separately.

To Check python is installed correctly please open cmd and run the following command.

C:\Users\HP>python

When you run the command then it will show you the currently installed version of python in your system.

Opening of Jupyter Notebook

Open the CMD and run the following command

C:\Users\HP>jupyter notebook

Now the Screen will look Like This

Now The Complete Code is-

Step 1:- Defining of Pandas Lib, Numpy Lib

Step 2:- Reading the CSV File. In this Case i have the location of                  CSV File is same as of my jupyter directory

Step 3:- Defining the Name of Columns

Step 4:- Printing of read data.

Step 5:- Definng of matplotlib 

Step 6:- Creating the plot parameter 

Step 7:- Running of Final Code.