Upgrade SQL Server 2014 to SQL Server 2016/17

In previous article “SQL Server Installation Guide for Beginners” will help you do a clean install. However, it is for SQL Server 2005 but steps are most of same for the latest version of the SQL Server,

First prepare for upgrade SQL Server and check the Supported Version and Edition Upgrades for SQL Server 2017.

Along this consider to check “Hardware and Software Requirements for Installing SQL Server”.

The first would be Prepare for upgrade by running Data Migration Assistant. It will let you know that it there any obstacles in upgrade to SQL Server 2016/17. Here is an nice explanation and migration steps to SQL Server.

Mount/insert the SQL Server 2016/17 installation ISO or media. double-click Setup.exe. Now it will open the SQL Server installation center.

Upgrade SQL Server 2014 to SQL Server 2016/17

To upgrade an existing instance of SQL Server, click Installation in the left-hand navigation area, and then click “Upgrade from a previous version of SQL Server”.

On the next step enter product key for the SQL Sever version/edition that you have. If you have MSDN static key then the existing key will work for you.

At “Accept License terms” page select the I accept the license terms check box, and then click Next.

Now setup checks for the installation files and any update available.

After setup files validation it checks for the Upgrade Rules

if there are no rule errors then at the next step, select the instance to upgrade.

Select features that you want to install/remove. By default the features to upgrade will be preselected on the Select Features page.

On the “Instance Configuration” page, you need to specify the Instance ID for the instance of SQL
Server.

On next step, the” Server Configuration” page the default service accounts are displayed for SQL Server services.

Press next to proceed to the “Full-text upgrade” page. There are three options – Import, Rebuild, and Reset. Specify the upgrade options for the databases being upgraded. For more information, see Full-Text Search Upgrade Options. For my case I was upgrading from SQL Server 2014 so I decided to import the existing one.

Now it will check the "Feature Rules" in the next step and display the summary after validation.

In my case, there was an error related SQL Server analysis service.

To Resolve the issue, we need to start the analysis services. Follow this step by step article to resolve this SQL Server Analysis Services start issue.

Now setup wizard advances to the upgrade page which displays a tree view of installation options that were specified during Setup. Click Install to continue.

At this step required prerequisites installed for the selected features to upgrade.

Click on “Upgrade” button to start the upgrade process and It will show the progress information.

When the upgrade process completes then you will see the installed features status. Pressing OK button on the “Computer restart required” dialog to disappear it.

Press “Close” button to finish the upgrade process.

Now we have done with SQL Server upgrade process to SQL Server 2016 and 2017.

Fix SQL Server Analysis Services start issue

Today I was upgrading an instance of SQL Server to SQL Server 2016. During the installation, I got below error at Feature Rules validation step.

From the error message I found that SQL Server Analysis Service is up. So below are the steps to resolve the SQL Server Analysis Service start issue:

Step 1: Press Win+R keys and type “compmgmt.msc” to open the component management console

Step 2: Expand the SQL Server Configuration node and then select SQL Server Services. On the right panel you will find the SQL Server Analysis Service and the state would be Stopped.

Step 3: Now right click on Start SQL Server Analysis Service if fails the solution is the go to logon and change the SQL Server Analysis Service and select start from the menu.

If everything is correct on your system then this service gets start on your system and Hope this way work for you in first attempt. But this not happened with me easily and I got error message “Windows could not start the SQL Server Analysis Services (MSSQLSERVER) service on Local Computer”:

In my case, after investigation on the system I come to know that the account which had permission on the Network account was by mistake deleted and I followed the below steps to start the service with different user account on the system:

1. Right clicked on the SQL Server Analysis Service and select “Properties”
   
2. On the Logon tab, browse the local administer account or another user account that has enough privilege to impersonate the service:
   

Now attempt to start the service. If provided account has permission and privilege then it will get start immediately.

I was not able to make it work after providing the administrator account credentials. The above image was after the service get start and to make it work I tried to search about the error message and found some reference about this message here.

I found information to get the further steps to fix the issue that when an SQL Server Analysis Services instance starts, it’s actually looking for a file called msmdsrv.exe located in $InstallDir\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\bin. So, to correct the things to start the service follow these steps:

1. Open File Explorer and go to this SQL Server Analysis Service installation folder.
2. Right click on bin folder and go to Security tab in properties window.
3. Click on Edit… and add the service impersonation account if you add another account as I did earlier. Otherwise provide permission to the user which is associated with Network service or added user account.

Now again attempt to start the service in component management window. This time it starts as like charm for me.

Hope this help to resolve issue for others. Cheers!

What is Microsoft SQL Operations Studio?

Introduction

SQL Operations Studio is a free, light-weight data management tool that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse;

Download and Install instruction for SQL Operations Studio Public Preview available here: Download SQL Operations Studio

Below is the feature list of SQL Operations Studio:

• Cross-Platform DB management for Windows, macOS and Linux with simple XCopy deployment
• SQL Server Connection Management with Connection Dialog, Server Groups, and Registered Servers
  
  
  
• Object Explorer supporting schema browsing and contextual command execution
  
  
• T-SQL Query Editor with advanced coding features such as autosuggestions, error diagnostics, tooltips, formatting and peek definition.
  
  
• Query Results Viewer with advanced data grid supporting large result sets, export to JSON\CSV\Excel, query plan and charting
  
  
• Management Dashboard supporting customizable widgets with drill-through actionable insights
• Visual Data Editor that enables direct row insertion, update and deletion into tables
• Backup and Restore dialogs that enables advanced customization and remote file system browsing, configured tasks can be executed or scripted
• Task History window to view current task execution status, completion results with error messages and task T-SQL scripting
• Scripting support to generate CREATE, SELECT and DROP statements for database objects
• Workspaces with full Git integration and Find In Files support to managing T-SQL script libraries
• Modern light-weight shell with theming, user settings, full screen support, integrated terminal and numerous other features

T-SQL code snippets

It also provides T-SQL code snippets which generate the proper T-SQL syntax to create databases, tables, views, stored procedures, users, logins, roles, etc., and to update existing database objects. To learn more, see Create and use code snippets.

(T-SQL) IntelliSense

SQL Operations Studio offers a modern, keyboard-focused T-SQL coding experience like SQL Server Management Studio that makes your everyday tasks easier with built-in features, such as multiple tab windows, a rich T-SQL editor, IntelliSense, keyword completion, code snippets, code navigation, and source control integration (Git).

Connection management (server groups)

Server groups provide a way to organize and share connection information for the servers and databases you work with. For details, see Server groups.

Integrated Terminal

Use your favorite command-line tools (for example, Bash, PowerShell, sqlcmd, bcp, and ssh) in the Integrated Terminal window right within the SQL Operations Studio (preview) user interface. To learn about the integrated terminal, see Integrated terminal.

Information from MSOS documentation: Microsoft SQL Operations Studio

Conclusion

It is a nice lightweight cross platform tool for SQL Developers and DBAs. I found it very intuitive and easy to use for managing database. Nice step by Microsoft toward OSS and cross platform development using such good Electron based tool rather SQL Server 2017 is also cross platform.

Rule "Same architecture installation" failed with Microsoft SQL Server Data Tools for Visual Studio 2013

Scenario:

I was installing Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 with a 64 bit SQL Server 2014 Express and Visual Studio 2013. The installation will not start because of a failing rule check. "Same architecture installation"

I have selected existing SQL Server instance to add features:

Then selected “SQL server Data Tools – Business Intelligence for Visual Studio 2013.

After that installation start running “Feature Configuration Rules” and I got an error saying that Rule “Same architecture installation” failed.

Solution:

I have searched for the problem and found that only 32-bit version of SQL Server Data Tools for Visual Studio 2013 published by Microsoft.

 

From MSDN forum and blog article:

Make sure you choose the "New Instance" option (strange as that might seem). Otherwise, you'll get an error that says the following:

Rule "Same architecture installation" failed.

On my machine x64 version of SQL Server was installed so it requires to do a new installation rather adding features to existing instance. I thought that It was due to the Operating System architecture difference but these tools require 32 bit instance of SQL Server.

Hope this help others to solve this strange issue.

RANDBETWEEN(m,n) in Sql Server?

Today I have to find random number between two numbers lots of the time in the stored procedures So I tried to create a function which return random numbers between two boundary numbers m and n. I tried to look for the solution and got below logic to implement the function.

Let m = 5 and n = 500 and method call would be RANDBETWEEN(5, 500). Then logic to find the random number would be as below:

• Use RAND() (which returns a value between 0 and 1 (exclusive).
• multiply by 298 (since you want a dynamic range of [300-3] = 297 + 1)
• add 3 to Offset
• and cast to INT?

Usage:

SELECT CAST(RAND() * 298 + 3 AS INT)

A Stored Procedure can be written like this if it supposed to be reuse in code more often:

CREATE PROCEDURE [dbo].[RANDBETWEEN]
@LowerBound int = 0 ,
@UpperBound int = 1 , 
@ret int OUT
AS
    BEGIN
    SET NOCOUNT ON;
    SELECT @ret = (CAST((RAND() * (@UpperBound - @LowerBound)) + @LowerBound AS INT));
RETURN ;
END;

Call can be made like this:

DECLARE @tmp INT;
EXECUTE [dbo].[RANDBETWEEN] 0,10, @ret=@tmp OUT ;
SELECT @tmp

To create a function I found that I have to create a View that make random method call and then function will do remaining operation to get the random number.

CREATE VIEW Get_RAND
AS
SELECT RAND() AS RANDNumber
GO

Then you can create a function like this (accessing the view with the SELECT RandomNumber... ) :

CREATE FUNCTION RANDBETWEEN(@LowerBound INT, @UpperBound INT)
RETURNS INT
AS
BEGIN
    DECLARE @TMP FLOAT;
    SELECT @TMP = (SELECT RandomNumber FROM Get_RAND);
    RETURN CAST(@TMP* (@UpperBound - @LowerBound) + @LowerBound AS INT);
END
GO

Then this function can be called as below:

SELECT [dbo].[RANDBETWEEN](1,10)

Restore SQL Server database with TSQL, RESTORE WITH REPLACE

First connect with your sql server using sqlcmd then use restore command as below.

C:\back>SQLCMD -S .\SQLEXPRESS2008

1> GO

1> SELECT NAME FROM SYS.DATABASES

2> GO

NAME

--------------------------------------------------------------------------------

------------------------------------------------

master

tempdb

model

msdb

TrainingWeb

(5 rows affected)

1> RESTORE DATABASE TrainingWeb

2> FROM DISK = 'C:\Back\traing_db.bak'

3> WITH  REPLACE,

4> MOVE 'traing_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS

     2008\MSSQL\DATA\TrainingWeb.mdf',

5> move 'traing_db_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXP

     RESS2008\MSSQL\DATA\TrainingWeb_log.ldf'

6> go

Processed 240 pages for database 'TrainingWeb', file 'traing_db' on file 1.

Processed 1 pages for database 'TrainingWeb', file 'traing_db_log' on file 1.

RESTORE DATABASE successfully processed 241 pages in 0.222 seconds (8.481 MB/sec).

Note: the logical name e.g. traing_db must match with the name of database for which you have

Created backup. Backup file name may be different but you must specify the backup database name

Specify the yellow marked entries.

Copying a database using BACKUP and RESTORE

The following example uses both the BACKUP and RESTORE statements to make a copy of
the AdventureWorks2008R2 database. The MOVE statement causes the data and log file to be
restored to the specified locations. The RESTORE FILELISTONLY statement is used to
determine the number and names of the files in the database being restored. The new copy of the
database is named TestDB.

BACKUP DATABASE AdventureWorks2008R2 

   TO AdventureWorks2008R2Backups ;

RESTORE FILELISTONLY 

   FROM AdventureWorks2008R2Backups ;

RESTORE DATABASE TestDB 

   FROM AdventureWorks2008R2Backups 

   WITH MOVE 'AdventureWorks2008R2_Data' TO 'C:\MySQLServer\testdb.mdf',

   MOVE 'AdventureWorks2008R2_Log' TO 'C:\MySQLServer\testdb.ldf';

GO

Running Transact-SQL Script Files by command line - sqlcmd

You can use sqlcmd to run a Transact-SQL script file. A Transact-SQL script file is a text file that can
contain a combination of Transact-SQL statements, sqlcmd commands, and scripting variables.

To create a simple Transact-SQL script file by using Notepad, follow these steps:

1.  Click Start, point to All Programs, point to Accessories, and then click Notepad.
2.  Copy and paste the following Transact-SQL code into Notepad:
     USE AdventureWorks2008R2;
     GO
     Select Emp.Address from EMP
    Where ID = 1
    GO

3.      Save the file as myScript.sql in the C drive.

To run the script file

1. Open a command prompt window.
2. In the Command Prompt window, type:

sqlcmd -S .\SQLExpress -i C:\myScript.sql

3. Press ENTER.

A list of Adventure Works employee names and addresses is written to the command prompt window.

To save this output to a text file

1. Open a command prompt window.
2.  In the Command Prompt window, type:

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

3.  Press ENTER.
No output is returned in the Command Prompt window. Instead, the output is sent to the EmpAdds.txt file.
You can verify this output by opening the EmpAdds.txt file.

Connect SQL Server using Command Line - Using the sqlcmd Utility (SQL Server Express)

You can manage your Microsoft SQL Server 2008 Express (SQL Server Express) databases by using the sqlcmd utility from the command prompt. To access the sqlcmd utility, click Start, click Run, and type sqlcmd.exe.

SQLCMD Syntax

The following arguments are available when you run the sqlcmd utility.

Sqlcmd

  [-U login id] [-P password] [-S server] [-H hostname]

  [-E trusted connection] [-d use database name] [-l login timeout]

  [-N encrypt connection] [-C trust the server certificate]

  [-t query timeout] [-h headers] [-s colseparator] [-w screen width]

  [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]

  [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"]

  [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel]

  [-W remove trailing spaces] [-u unicode output]

  [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile]

  [-f <codepage> | i:<codepage>[,o:<codepage>]]

  [-k[1|2] remove[replace] control characters]

  [-y variable length type display width]

  [-Y fixed length type display width]

  [-p[1] print statistics[colon format]]

  [-R use client regional setting] [-b On error batch abort]

  [-v var = "value"...]

  [-X[1] disable commands[and exit with warning]]

  [-? show syntax summary] 

Connecting to SQL Server Express 

sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword 
 

When you connect to a SQL Server Express server, you must specify the server name and, if SQL Server Express is installed in a named instance, the instance name. By default, sqlcmd uses Windows Authentication. If you are connecting to the SQL Server Express server by using SQL Server Authentication, you must also provide the logon information for connecting to the SQL Server Express server. For example, if you are connecting to a default installation of SQL Server Express on a server named server1, you might use the above arguments:

sqlcmd -S server1\SQLExpress

If you are logged on with a user account that is trusted on the server that is running SQL Server Express, you can omit the -U and -P arguments.

The following example shows how to use the sqlcmd utility to connect to an instance of SQL Server Express, named SqlExpress, on a server named SqlServer1:

Sqlcmd -S SqlServer1\SqlExpress

After you are connected, you can issue other commands to manage the instance. For example, the following commands will display all currently installed databases. This is a convenient way to find the logical name of an attached SQL Server Express database:

SELECT name from sys.databases

Go 

For More Information visit: MSDN Article on SQLCMD

How to get version info from SQL backup (.bak) file

Get version info from SQL backup
use this little code to find the .bak file information
USE NIRDB
RESTORE HEADERONLY
FROM DISK = N'D:\VSS\NIR.bak'WITH NOUNLOAD;
 Version number to SQL Server version, here's two:
611 = SQL Server 2005
655 = SQL Server 2008

RESTORE HEADERONLY - contains a list of backup header information for a backup device
RESTORE FILELISTONLY - contains a list of the data and log files contained in the backup
RESTORE LABELONLY - contains information about the backup media
Following are sample outputs from each of the commands.  These first three outputs show you the
complete output from running the commands against a backup file containing one full backup of the
AdventureWorks database.  As you can see there is a lot more information in the files that may or
may not be useful to you.
RESTORE HEADERONLY FROM DISK='C:\Backup\Adv_Full.bak'

BackupName	AdventureWorks-Full Database Backup
BackupDescription	NULL
BackupType	1
ExpirationDate	NULL
Compressed	0
Position	1
DeviceType	2
UserName	EDGENB2\Sysadmin
ServerName	EDGENB2\TEST1
DatabaseName	AdventureWorks
DatabaseVersion	611
DatabaseCreationDate	38985.72449
BackupSize	173091840
FirstLSN	41000000054400000
LastLSN	41000000056800000
CheckpointLSN	41000000054400000
DatabaseBackupLSN	41000000041600000
BackupStartDate	1/3/2007 8:15:41 PM
BackupFinishDate	1/3/2007 8:15:41 PM
SortOrder	52
CodePage	0
UnicodeLocaleId	1033
UnicodeComparisonStyle	196609
CompatibilityLevel	90
SoftwareVendorId	4608
SoftwareVersionMajor	9
SoftwareVersionMinor	0
SoftwareVersionBuild	1399
MachineName	EDGENB2
Flags	512
BindingID	5956B629-86DF-4000-BAC0-52194A773D3B
RecoveryForkID	B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
Collation	SQL_Latin1_General_CP1_CI_AS
FamilyGUID	B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
HasBulkLoggedData	0
IsSnapshot	0
IsReadOnly	0
IsSingleUser	0
HasBackupChecksums	0
IsDamaged	0
BeginsLogChain	0
HasIncompleteMetaData	0
IsForceOffline	0
IsCopyOnly	0
FirstRecoveryForkID	B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
ForkPointLSN	NULL
RecoveryModel	FULL
DifferentialBaseLSN	NULL
DifferentialBaseGUID	NULL
BackupTypeDescription	Database
BackupSetGUID	1389292F-F593-425D-BD36-325FCEA0E02A

RESTORE FILELISTONLY FROM DISK='C:\Backup\Adv_Full.bak'

LogicalName	AdventureWorks_Data	AdventureWorks_Log
PhysicalName	C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Data.mdf	C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Log.ldf
Type	D	L
FileGroupName	PRIMARY	NULL
Size	188678144	2097152
MaxSize	35184372080640	2199023255552
FileId	1	2
CreateLSN	0	0
DropLSN	0	0
UniqueId	94EDC99D-D0E0-4146-95DA-1756D6C92348	EB9DB2B3-BE70-4F76-8345-7FF07FB705C7
ReadOnlyLSN	0	0
ReadWriteLSN	0	0
BackupSizeInBytes	172163072	0
SourceBlockSize	512	512
FileGroupId	1	0
LogGroupGUID	NULL	NULL
DifferentialBaseLSN	41000000041600000	0
DifferentialBaseGUID	6493F201-EBBA-47DD-BBDA-83A2772A8DA3	00000000-0000-0000-0000-000000000000
IsReadOnly	0	0
IsPresent	1	1

RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'

MediaName	NULL
MediaSetId	23979995-927B-4FEB-9B5E-8CF18356AB39
FamilyCount	1
FamilySequenceNumber	1
MediaFamilyId	86C7DF2E-0000-0000-0000-000000000000
MediaSequenceNumber	1
MediaLabelPresent	0
MediaDescription	NULL
SoftwareName	Microsoft SQL Server
SoftwareVendorId	4608
MediaDate	1/3/07 8:15 PM
MirrorCount	1

If we have a backup file that contains multiple backups, using the HEADERONLY option shows us
the information for each of the backups.  Following is a condensed view of the RESTORE
HEADERONLY output.  As you can see there are three backups in this file; one full backup and two
transaction log backups.  This information can be determined by the BackupType.

BackupName	AdventureWorks-Full Database Backup	AdventureWorks-Transaction Log Backup	AdventureWorks-Transaction Log Backup
BackupDescription	NULL	NULL	NULL
BackupType	1	2	2
Position	1	2	3
BackupSize	173091840	74752	8192
FirstLSN	41000000054400000	41000000054400000	41000000059200000
LastLSN	41000000056800000	41000000059200000	41000000059200000
CheckpointLSN	41000000054400000	41000000054400000	41000000054400000
DatabaseBackupLSN	41000000041600000	41000000054400000	41000000054400000
BackupStartDate	1/3/07 8:15 PM	1/3/07 8:39 PM	1/3/07 8:40 PM
BackupFinishDate	1/3/07 8:15 PM	1/3/07 8:39 PM	1/3/07 8:40 PM
BackupTypeDescription	Database	Transaction Log	Transaction Log
BackupSetGUID	1389292F-F593-425D-BD36-325FCEA0E02A	1DAB6FAA-14AD-4C3C-8081-6A15CB170782	285DC2A1-1E89-44A5-B9ED-373821C94054

So how does this information help you restore your databases?
When your backup files contain multiple backups in one file you need to specify the position of the file
that you are restoring.  This option for the RESTORE command is FILE, but this number corresponds
to the Position value.  So if we want to restore these files using the RESTORE command we would
issue the following three commands one for each of the backups using the value that is in the Position
from the HEADERONLY output..
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 1, NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 2 , NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 3, RECOVERY
In addition to being able to restore multiple backups from one backup file, we can also use the output
from the FILELISTONLY to determine where the default locations will be for the data and log files. 
If you take a look at the output above from the FILELISTONLY command and look at the values in
the LogicalName and PhysicalName you will see the directory where the database was stored was
in the "C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\" directory.  If you
just do a RESTORE the data and log files will be created in this directory.  If the directory does not
exist or if you want to specify another directory or file name you need to use the WITH MOVE option
of the RESTORE command.  This can be done as follows:
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 1, 
RECOVERY, 
MOVE 'AdventureWorks_Data' TO 'J:\SQLdata\AdventureWorks_Data.mdf', 
MOVE 'AdventureWorks_Log' TO 'X:\SQLlog\AdventureWorks_Log.ldf'
it's a nice artical of MSSQL- tips, thanks for giving such a valuable information.