Advertisements

Archive

Archive for the ‘Others’ Category

SQL Server 2016 Certification Path


 
Microsoft has made few changes to its Certification path for the new version of SQL Server i.e. SQL Server 2016.

And with this post I’m trying to collate and put all exams and certifications in concise and clear manner. Would be happy to accept any comments, changes and suggestions !!!
 

–> The MCSA i.e. “Microsoft Certified Solution Associate” level now contains the new “MCSA: SQL Server 2016” certification, and this replaces the older “MCSA: SQL Server 2012/14” one.
 

–> And the top MCSE i.e. “Microsoft Certified Solutions Expert” level now contains the new “MCSE: Data Management and Analytics” certification. This also replaces the following 2 existing certifications (but retains the underlying exams to earn this MCSE level, which I’ve explained below):
– MCSE: Data Platform
– MCSE: Business Intelligence


 

–> Here are the details of exams at both the levels.

Level 1. “MCSA: SQL Server 2016”: Now to earn this level you need to give either of the following certifications:

   1. MCSA: SQL 2016 Database Development certification
       – Exam [70-761], Querying Data with Transact-SQL
       – Exam [70-762], Developing SQL Databases

   2. MCSA: SQL 2016 Database Administration certification
       – Exam [70-764], Administering a SQL Database Infrastructure
       – Exam [70-765], Provisioning SQL Databases

   3. MCSA: SQL 2016 Business Intelligence Development certification
       – Exam [70-767], Implementing an SQL Data Warehouse
       – Exam [70-768], Developing SQL Data Models

 

Level 2. “MCSE: Data Management and Analytics”: Now to earn this level you need to first get an MCSA on either of the above 3 certifications, or “MCSA: Data Science”. Then need to pass anyone of the below exam:

   – Exam [70-473], Designing and Implementing Cloud Data Platform Solutions
   – Exam [70-475], Designing and Implementing Big Data Analytics Solutions
   – Exam [70-464], Developing Microsoft SQL Server Databases
   – Exam [70-465], Designing Database Solutions for Microsoft SQL Server
   – Exam [70-466], Implementing Data Models and Reports with Microsoft SQL Server
   – Exam [70-467], Designing Business Intelligence Solutions with Microsoft SQL Server


Advertisements

Which SQL Server Certification should I take: 98-364, 70-461 or 70-761 ? – MSDN TSQL forum

February 18, 2017 3 comments

 
–> Question:

I just started learning SQL Server, and I want to get certified, I checked internet, but I got confused.

There is exam 98-364, 70-461, 70-761, and i want to get MCSA in SQL

could some one explain to me what should i do …
 

–> My Answer:

>> MCSA or Microsoft Certified Solutions Associate in SQL Server can be earned on following areas with respective exams:

– Database Development (761, 762)
– Database Administration (764, 765)
– Business Intelligence (767, 768)

>> Exam 98-364 is to get Microsoft Technology Associate (MTA) certification. It is the most basic exam that focuses just on basic SQL, and is for people new to technology, who need to validate their knowledge of fundamental concepts before moving on to more advanced certifications. This exam covers:

1. RDBMS, DBMS concepts and terms, like Normanization, PK, FK, etc.
2. Plain SQL querying knowledge, like DDL, DML, etc. and indexes.
3. Simple DBA stuff, like security, backup/restore, etc.

Link: https://www.microsoft.com/en-in/learning/exam-98-364.aspx

>> Exam 70-761 is also an advanced exam which is based upon SQL Server 2016, which is the latest version of SQL Server as of today. To earn MCSA you have to take 70-762 exam as well as mentioned above, check this link for MCSA SQL Server 2016: https://www.microsoft.com/en-in/learning/mcsa-sql-2016-certification.aspx

>> Exam 70-461 is also an advance level exam but now older based upon SQL Server 2012 or 2014. Check this link for MCSA SQL Server 2012/2014: https://www.microsoft.com/en-in/learning/mcsa-sql-certification.aspx

Please note: Exam 98-364 is not required here, and as mentioned above its just to make sure you have knowledge in working with basic SQL Querying.

Ref link.


This blog just crossed 2 million hits – Thanks !!!

January 22, 2017 3 comments

 
Today this blog crossed 2 million hits !!!

Last time the first [1 million] milestone was achieved at June 2015, and it took almost 5 years to achieve this.

This time in Jan 2017 the 2 million milestone got achieved in just 1 year 6 months (to get extra 1 million hits).
 

–> Here is the stats of “Year by Year” hits:

Let’s target the 3 million milestone i.e. next 1 million hits in 1 year, in year 2017 🙂
 

I would like to thank all my readers and visitors for your continued support, comments and suggestions !!!


Connect SQL Server on Linux with SSMS from a Windows machine

January 6, 2017 1 comment

 
In [previous post] we saw how to install & setup SQL Server on Linux. We used PuTTY to connect to the Linux Azure VM and query SQL Server there.

Here in this post we will try to connect to SQL Server on Linux via SSMS from a Windows machine.
 

So to connect remotely to SQL Server on an Azure VM, you have to configure an inbound rule on the network security group. The rule allows traffic on the port on which SQL Server listens (default is 1433).
 

–> Setup Inbound Rule:

1. You need to go to portal.azure.com and login with your credentials. On the Dashboard you will see your Linux VM as shown below:

linux-ubuntu-on-azure-07
 

2. Click on the Linux VM widget, it will open up and list out the Settings, click on Network Interfaces, and select the available Network Interface from the list.

install-sql-on-linux-09
 

3. Now click on the Network Security Group:

install-sql-on-linux-11
 

4. On the Network Security Group page, select the Inbound security rules, and click on the “+” button create new for SQL Server:

install-sql-on-linux-12
 

5. On the Add inbound security rule page, provide a name to this rule, select “MS SQL” under Service drop-down, and click OK:

install-sql-on-linux-13
 

–> Connect using SSMS:

6. Now from any Remote machine or even you PC which has SSMS installed and have internet connection, try connecting the SQL Server Linux Instance:

– Server Name: provide the IP address of the Linux VM (you can get it by opening the “Linux VM widget” and clicking on “Connect” button at top)

– Authentication: should be SQL Server Authentication

– Login: “SA”

– Password: apply the same password that you set it up while Configuring SQL Server on Linux.

install-sql-on-linux-14
 

7. On successful authentication you will see the Object Explorer showing the SQL instance that’s running on a Linux machine. On a glance you can’t make out any difference b/w a Linux SQL instance or a Windows SQL instance.

Thus I executed the SELECT @@version statement which shows the SQL Server version and on which Operating system its running.

You can even see the Database SQLdbOnLinux that we created by connecting from the PuTTY client.

install-sql-on-linux-15
 

That’s it for now, will write more about SQL on Linux on coming updates from Microsoft !!!

–> You can also watch this on YouTube:


Install and Configure SQL Server 2017 on Linux Azure VM

January 4, 2017 2 comments

 
SQL-Loves-Linux_2_Twitter-002-640x358

 
In my [previous blog] post we saw how to Create a new Linux VM on Azure. After creating it we connected it from a Windows workstation by using PuYYT tool and tested one Unix command.
 

Here in this post we will see how we can install SQL Server on Linux.

Microsoft is working on a new SQL Server vNext version that will work both on Windows as well as Linux. So with the recent [CTP 1.x] release we can see how does it look like on both the Operating Systems and get some hands on with it.
 

–> Get the Repository and Install SQL Server:

1. Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2. Register the Microsoft SQL Server Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

3. Re-synchronize the Package index files from their sources, to get information of the newest versions of packages and their dependencies, and finally Install MSSQL-Server package:

sudo apt-get update
sudo apt-get install -y mssql-server

After Install it will ask you to run SQL Server setup by following message:

Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R)

4. Now post install, we need to configure SQL Server and set the SA password, and SQL services.

sudo /opt/mssql/bin/sqlservr-setup

The Configuration gives following prompts in between:

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password…

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y

5. Post Configuration you can verify that the service is running:

systemctl status mssql-server


 

–> Install Tools: This will install the command-line tools (sqlcmd & BCP), Microsoft ODBC drivers, and their dependencies.

6. Re-synchronize the Package index files, and Install MSSQL-Tools:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
sudo ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

 

–> Connect to SQL instance:

9. We will use sqlcmd to connect to SQL Server vNext on our Linux VM. Run the sqlcmd command with following params:
– S: Server Name
– U: User Name
– P: Password

sqlcmd -S localhost -U SA -P ”

 

–> Run SQL Queries:

10. Now after connecting to the SQL Linux instance, let’s test come queries:

We will check the Version of SQL Server and list out system Databases:

select @@version
go

select database_id, name, create_date from sys.databases
go

Output of version statement:
Microsoft SQL Server vNext (CTP1.1) – 14.0.100.187 (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)

11. Let’s do more and Create a new Database, new Table and insert some sample records:

CREATE DATABASE SQLdbOnLinux
go

use SQLdbOnLinux
go
--Changed database context to 'SQLdbOnLinux'.
CREATE TABLE Test (i int, name varchar(25))
go
insert into Test values (1, 'Manoj')
insert into Test values (2, 'Saurabh')
go

--(1 rows affected)

--(1 rows affected)
select * from Test
go
--i           name
------------- -------------------------
--          1 Manoj
--          2 Saurabh

--(2 rows affected)

I think you would get amazed like me after using SQL Server on a Linux environment.

In my [next post] I’ll show how we can connect this SQL Linux instance from a Windows workstation via SSMS !!!

–> You can also watch this video on YouTube: