Home » Posts tagged 'dcode'

Tag Archives: dcode

Introduction to Microsoft SQL Server Management Studio (SSMS)

[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook 
& YouTube.]

Microsoft SQL Server Management Studio IDE (Integrated Development Environment) is a powerful GUI tool with a long list of features and options. SSMS is very useful and user friendly for all users like DBA, Developers, Testers, or Students.

Top Action You can do using SSMS:-

  • Connect to Local \ Remote SQL Server Database Engine \ Analysis Services \ SSIS (Integration Services) \ SSRS (Reporting Services) Instance
  • Explore Connected Server Properties and Objects like Logins \ Linked Servers
  • Explore Database list, Database properties & objects
  • Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans
  • Activity Monitor
  • Lunch other SQL Server tools like SQL Profiler \ DTA
  • Manage SQL Server Agent and Jobs
  • Connect \ Create \ Manage Registered Server
  1. Connect to Local \ Remote SQL Server Database Engine \ Analysis Services (SSAS) \ Integration Services (SSIS) \ Reporting Services (SSRS) Instances using SSMS
  • When you lunch SQL Server Management Studio (SSMS), “Connect to Server” window pop up for server and credentials details by default for Database engine Server Type. In case, if don’t or you had closed or you want to connect other server types, You can open it from Object Explorer > Connect > Select Desired Server Type “Database Engine” as below.

  • Enter Server Name: SSMS prompts you to enter the SQL instance name to connect. It can be a local or remote SQL instance. In a live environment, one SSMS can be used to monitor and manage remote SQL instance for the complete connecting environment.
  • Select authentication Type and Enter User Details: After mentioning the SQL instance name, you need to select the authentication type. Windows authentication or SQL authentication. In windows, your currently used windows AD access will be used to connect or in SQL authentication, you will provide SQL login details. Once you fill all details, It will connect you with the mentioned instance.

Sample Connection:-

  1. Explore Connected Server Properties and Objects like Logins \ Linked Servers
  • To explore Server properties, Right Click on Instance Name and Click Properties. It will open windows with the list of server-level configuration and properties. You can review and make the required changes. Some Major and mostly used configuration from server properties:-
General Tab for Server Edition\ Server Version \ OS Version \ RAM \ Processors details
Min \ Max Memory Configuration in Memory Tab
SQL Server Authentication Mode in Security Tab
Database files and Backup default location in the Database Settings tab
MaxDop (Max Degree of Parallelism) and CTP (Cost Threshold for Parallelism) in advance tab
  • Server Objects like Logins \ Triggers \ Linked servers be browed and reviewed.

  1. Explore Database list, Database properties & objects
  • To Explore a list of available databases and Database objects, You need to browse like below:-

  • To review and Configure Database properties, Right Click on Database Name & go to properties. Some of the major database properties are:-
General Tab for Database Owner, Creation Date, Total & Free Space, Last backup timestamp details
Files Tab for Files details and modifications
Options Tab for Checking and changing Collation, Recovery Model, Compatibility Level, etc.
  1. Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans

  • Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File)
Results in Grid
Results in Text
Results in File (You will prompt to select a location to save the file with execution results)
  • Intellisense: SSMS IntelliSense is an interesting feature, this provides runtime suggestions for object names and reduce query-writing time. Intellisense populates all objects in the buffer and shows then as an option.

  • Estimated & Actual Execution plan of queries
  1. Lunch other SQL Server tools like SQL Profiler \ DTA

  1. Activity Monitor: Activity Monitor can be started by right click on instance name. It’s a GUI monitoring wizard for activities & sessions running on the server.

You can drill down multiple options on Activity Monitor Page for details of each segment.

List of features and option in SSSMS is countless, I suggest to download and install it to explore.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

SQL Server Sample Databases

[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook
& YouTube.]

To start your learning of SQL Server and testing of new features, Microsoft provided some of ready to use sample databases. These are very useful for students and new learners.

Microsoft provides 2 sample databases which can be found at: https://docs.microsoft.com/en-us/sql/samples/sql-samples-where-are?view=sql-server-ver15

1) AdventureWorks: One of the oldest sample databases, Microsoft keeps updating it with new versions. Directly available on MS site for download.

2) Wide World Importers: New entry in sample databases and can be downloaded from Github.

When you click on the yellow highlighted link, It will redirect you to Github and you can download backup files from there.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

Download SQL Server Developer / Evaluation / Express Editions

[MSSQLFUN & Decote ITeS becomes a family of 1400 individuals across the globe. Please join us on
Facebook
& YouTube.]

Click here to view on YouTube

Today, I will take you to the steps of downloading Download SQL Server Developer / Evaluation / Express Editions. As discussed in my previous blog Understand SQL Server Editions and Components, SQL Server Developer / Evaluation / Express are free to use editions with fewer limitations and restrictions.

 

Step 1- Open Link (https://www.microsoft.com/en-au/sql-server/sql-server-downloads).

Step 2- You Browse to SQL Server 2019 Setup pages, it will give you the option of downloading Enterprise Evaluation, Developer, and Express edition.

You click on the desired option to download the setup file. We will download the Enterprise Evaluation Edition.

Step 3- Sign up to download SQL Server 2019 evaluation

Step 4- Download link is ready, please click

Step 5-

  • Click the file downloaded from the last step, “SQL2019-SSEI-Eval.exe”. Click to install it.
  • Chose the “Download Media” option to have offline setup.

Step 6-

  • Select Language
  • Choose CAB file to download
  • Select the location of the download file
  • Click Download

Step 7- These are the downloaded files and these are not ready to install setup. We need to unbox the setup file by giving exe.

Step 8- When you execute it, it will ask for the location to save setup files

Step 9- Setup files are ready to install.

We will use this setup to install SQL Server 2019 instance upcoming lesson.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun
http://mssqlfun.com/feed

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg
https://www.linkedin.com/in/rohitgarg1986/
%d bloggers like this: