BLOGS

ByHariharan Rajendran

Named SQL Server in Azure

There is no option to create a your own named server in Azure management portal.

While creating a SQL database, azure will create a SQL Server with random name. This is not a good practice in real time environment as we are maintaining the proper naming conventions.

We can create a  our own named SQL Server in Portal.azure.com

Create a new database and select the “Create a new Server” option to create your own named server.

image

ByHariharan Rajendran

Steps to Upgrade Azure SQL Server

Azure has released a new version of SQL Server V12.

If you are using V2 SQL Server then you can upgrade to V12. This upgrade option is available in Preview Portal.

Follow the below steps to upgrade your SQL Server

Step 1: Visit Preview Portal and open your SQL Server then select server version.

image

Step 2: select the “Upgrade this server” in right hand side window of your SQL Server

image

Step 3: New window will ask you to enter your SQL Server name

image

Step 4: Enter your SQL Server name and select “OK”. Upgrade process will be initiated.

image

 

Wait for sometime and your SQL Server will be upgraded to newer version V12.

To compare the feature with older and newer version, visit my previous article.

ByHariharan Rajendran

Azure SQL Server

Azure has released a new version of SQL Server which is named as V12. The previous version of SQL Server is V2.

If we create a SQL database using Quick create method in manage portal then there is no option to choose the latest version V12 server. This is possible when you select a custom create option.

Steps to choose latest version,

1. Choose the custom create option and fill the required details and select new server.

image

2. Select the check box to enable the latest version.

image

To know about the latest features in V12, visit here.

Check the preview portal where we can see the difference in features between V2 and V12.

Features in V2 & V12

image  image

ByHariharan Rajendran

SQL Server Management Studio Firewall Settings

We need to follow the below steps to connect the azure based SQL Server in SSMS

1. Add the client IP address in azure firewall

2. Use the azure SQL Server name with proper credentials.

The above steps should be carried out until SQL Server 2016 to access the SQL databases in SSMS.

If IP is not configured then SSMS will not allow to connect, it will through the below error message.

image

 

SQL Server 2016 CTP 3.3 has built in feature to add the IP in azure SQL Server firewall from SSMS itself.

Follow the below steps,

1. There is no IP configured in Azure SQL Server Firewall

image

2. Enter the server name and credentials and click connect. It will open a Azure account sign in window

image

 

image

3. Server will be connected and can access the databases. Client IP address added into firewall.

image

ByHariharan Rajendran

DTU in SQL Azure Database

DTU is called “Database Throughput Unit”. It is a unit of measure in SQL Database. It also represents the relative power of the database transaction.

It is an abstraction of,

1. CPU

2. Memory

3. Read

4. Write

We have different service tiers based on DTU.

Check the below URL to compare the DTUs in each tier

https://azure.microsoft.com/en-us/pricing/details/sql-database/?b=16.50

ByHariharan Rajendran

Temporary Tables & Table Variable

In SQL Server, temp tables are created at run time. It is just like a normal table and it will support all the operations but these tables are created inside temp database. The scope of the table is only on session & connection.

There are two types of temporary tables,

  1. Local Temp Table
  2. Global Temp Table

Local Temp Table

It will be available only on SQL Server session or connection. It means, if the session is closed or connection is out then this temp table will not be accessible, it will be deleted.

This will be accessible only one user who creates the table.

Local Temp table name is started with “#” sign.

Syntax

CREATE TABLE #LocalTemp

(

 Columns

)

Example

–Create a local Temp Table

CREATE TABLE #LocalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into #LocalTemp values (1,‘Hari’‘Test@Test.com’‘123-245-356’)

Go

–Insert value from another table

Insert into #LocalTemp 
(UserIDName, EmailIdPhonenumber)

(SELECT UserId, NameEmailId, Phonenumber from dbo.USERS)

Go

Try to open a new query window and access the above table then see the result by yourself.

TempTable

Global Temp Table

This table will be available for all SQL Server sessions. This can be accessible by multiple users with the same connections. It will be deleted when a connection is closed or all the sessions are closed.

Global Temp table name is started with “##” sign

Syntax

CREATE TABLE ##GlobalTemp

(

 Columns

)

GO

Example

–Create a local Temp Table

CREATE TABLE ##GlobalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into ##GlobalTemp values (1,‘Hari’‘Test@Test.com’‘123-245-356’)

Go

–Insert value from another table

Insert into ##GlobalTemp 
(UserIDName, EmailIdPhonenumber)

(SELECT UserId, NameEmailId, Phonenumber from dbo.USERS)

Go

Open multiple query window and access this table then close the connection and connect again. Then try to access the table by yourself.

Table Variable

Apart from temp tables, we have table variable and the scope of this table variable only with the batch of script.

It will be dropped once it comes out of the batch.

This is also created on temp database but not on memory. This allows us to create primary key on table as like normal table when declare a table variable but the difference is we cannot create cluster index.

Example

–First Batch Start

–Create a table variable

DECLARE @Users TABLE

 (

 UserID int
identity (1,1),

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

 )

  –insert values from another table

 Insert into @Users
(Name, EmailIdPhonenumber)

 SELECT  NameEmailId, Phonenumber from dbo.USERS

 –Select the records

 SELECT * FROM @Users

 –First Batch End …………..

 –Second batch start

 SELECT * FROM @Users –gives error

 Go

 –Second Batch End….

ByDr. SubraMANI Paramasivam

Install R Studio 0.99.491

You can download the latest R Studio 0.99.491 and install for FREE in this link. However if you need support then you need to agree to a monthly term to support you and your production system, and you can compare it from this link about the pricing for your desktop and server.

I have downloaded the RStdio-0.99.491 and have started installing and provided you the below screenshots for your reference.

Install_RStudio_099_01

In here, you have option to choose your folder and it also gives you the amount of space required for this installation. I know, I am running out of space, but I am doing this in my virtual machine.

Install_RStudio_099_02

Here you get option to add to the start menu and create shortcuts. I chose the default options in below screenshot.

Install_RStudio_099_03

As you can see below, the extraction and installation takes place for about couple of minutes.

Install_RStudio_099_04

That’s it. We are now done with the installation of R Studio.

Install_RStudio_099_05