Niranjan Singh's coding blog

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.

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!

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

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.

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)