Home » Posts tagged 'Decode ITES'

Tag Archives: Decode ITES

SQL Server Authentication

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

We will be discussion SQL Server Authentication and below topics:-

  • What is Authentication?
  • Type of authentication in SQL Server (SQL & Windows)
  • Windows Authentication
  • What is Windows authentication?
  • Advantages & Disadvantages

SQL Authentication

  • What is SQL authentication?
  • Advantages & Disadvantages

SQL Server Authentication Modes

  1. What is Authentication?

Authentication, The process of identity verification of the user. In Laymen terms, Attention is the process to check “Who are you?”. It can be based on user-id & password or token-based or certificate-based. Authentication is the key to allow only authorized users can connect to access the data and system.

Only user with correct authentication details able to connect.

  1. Type of authentication in SQL Server (SQL & Windows)

Microsoft supports 2 types of authentications, SQL, and Windows authentication. For each login created on SQL Server, it should be part of either authentication.

  1. Windows Authentication

Windows Authentication depends on Active Directory (AD) to authenticate users before they connect to SQL. Windows auth use series of encryption to ensure no leak of sensitive data. It is the recommended authentication mode because AD is the best way to manage password policies and user and group access to applications in your organization. User Id and password stored in Active Directory. You can run SQL Server only on Windows Authentication called Windows Authentication Mode.

Advantages of Windows Authentication:

ü All in One Domain: When your environment is on one domain, both DB and application are hosting on the same domain. Windows authentication is the best and secure way of DB communication.

ü Domain Controller: Here, we had Domain Controller to manage logins creation \ modification and authentications.

ü Password Policies: Windows logins must bear with password policies. Windows logins can not skip it. This makes the user password more secure and updated.

ü DBA relief: DBA no more needed to manage users and passwords. This task is completely managed by domain admins in windows auth.

ü Windows Group: Windows give functionality to create groups and that group can be added to SQL server to give access to all users in the group in one go.

Disadvantages of Windows Authentication:

ü No Control: DBA has no control over logins, it completely managed by domain admins.

ü Windows Group: Windows groups has a disadvantage as well because membership of the group is hidden to DBA. DBA will not be aware of who is part of groups or when users added or removed from the group.

  1. SQL Authentication

SQL Server Authentication works by storing usernames and passwords in the “Master” database server. It can be used in situations where Active Directory is not available., but, whenever possible, use Windows Authentication exclusively. You can use SQL Server and Windows Authentication at the same time called Mixed Mode.

You can create multiple users under SQL authentication to provided different users different access as per their requirement.

Advantages of SQL Server Authentication

ü Legacy Application Support: You can keep using your legacy application with support of SQL authentication.

ü Vendor Application Support: Vendor or 3rd Party application which are not built specifically to use SQL Server can be used with SQL Server using SQL authentication

ü Cross Operating System: SQL authentication is best to use when the environment had multiple types of operating systems like Windows, Linux, etc.

ü No Domain \ Workgroup \ Non-Trusted Domain Environment: SQL authentication is best to use while working in No Domain, Workgroup, Non-Trusted Domain Environment where users cannot be authenticated using Windows domain controller.

Disadvantages of SQL Server Authentication:

ü Multiple User Name and Password for Users: Let’s consider a user who needs access to multiple database instances. In SQL authentication, the User will have separate login and password for each instance. This will be difficult to manage and annoying.

ü Multiple User Name and Password for DBAs to Manage: Let consider, You are a team of 5 DBAs and using 50 SQL Server instances. This lead to 250 user ID & Password management for DBA.

ü Enforcing Password Policy: SQL Server logins can be skipped from enforcing password policy. These lead to week passwords and no regular change of password.

ü Application or user pass SQL login and password to connect. This communication can be hacked and user id & passwords can be compromised.

  1. SQL Server Authentication Modes

Based on available authentication types, SQL Server supports 2 types of authentication modes.

  • Windows Authentication Mode:- This is the default one and preferred from Microsoft.
  • Mixed Mode: Mixed mode supported Windows authentication along with SQL authentication. Both types (windows & SQL) logins can work under this model.

How to check the current authentication mode?

Right Click on Server Instance Name in Object Explorer > go to Properties > Security Tab

To change authentication mode, you can change the selection from this screen and click ok. This change needs SQL Service to restart to take into effect.

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/

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/

Step By Step Installation of SQL Server 2019 with all features

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

Today, I will take you to step by step installation of SQL Server 2019 with all features.

You can go through my previous post for downloading Download SQL Server Developer / Evaluation / Express Editions, YouTube Link.

Step 1: Locate Setup.exe and run it as Administrator.

Step 2: Setup will open SQL Server 2019 installation Center

Step 3: Go to Installation option and click “New SQL Server stand-alone installation or add features to an existing installation”

Step 4: Select free edition you like to install. The single setup gives you the option of Evaluation \ Developer \ Express edition to install. As discussed in my previous blog Understand SQL Server Editions and Components (YouTube), SQL Server Developer / Evaluation / Express is free to use editions with fewer limitations and restrictions.

Step 5: Accept EULA (End User License Agreement)

Step 6: Setup will perform some rule check before proceeding. In case of any issues, It will stop and ask you to rectify it.

Step 7: Microsoft gives you the option of automatic update of SQL Server with the latest security and other important updates along with windows. I suggest skipping it because it’s not recommended for Production systems and requires the internet which is very rare for servers due to security concerns.

Step 8: In case you accept for Microsoft Update in the previous step and system has internet connectivity, Setup will check available SQL Server upgrades. Setup will download those upgrades (SP, CU) and install them with SQL Setup installation. This will save you the time of separate installation. You try this on the local system and again as mentioned above, not recommended for production systems.

Step 9: Setup will install some installation files before to begin the actual installation

Step 10: Setup will run installation rules \ checks. In case of any issues, it will stop and ask you to rectify it.

Step 11: Feature Selection. In the current window, You can select the features you like to install. For the demo, We will install all versions. These windows will also show the space requirement for setup installation based on features selected. Also, you can change the installation and shared feature directory as per your requirements.

Step 12: Based on the selected features, Setup will run a rule checker.

Step 13: SQL instance name. SQL gives you the option of default or named SQL Server instance. Once the system can have only one default instance and multiple names instance. We will install the named instance today. This window will also review your system and display any previously installed SQL Server instances on your system.

Step 14: PolyBase Configuration, You can leave it as it is and move to next.

Step 15: Select features need JAVA, You can allow setup to install JAVA or mentioned Java path in the case already installed.

Step 16: Server Configuration – Service Account Window.

  1. You can change the service account of any services and start-up type
  2. As per your requirement, Select “Grant Perform Volume Maintenance Task to SQL Service Account”. In Case of service, the account is part of the local admin group this will by default assigned else recommended to provide it.

Step 17: Server Configuration – Collation Window. SQL Server selects collation based on default language and location of the system, you can change it as per your requirements. I suggest not to change it without testing and confirmation. This will decide how SQL Server will deal with tests. Case sensitive or case insensitive means upper caps & small caps will be treated equally or differently OR Data will be sorted in ascending order or descending order.

Step 18: Database Engine Configuration – Server Configuration.

  1. Select authentication mode for your SQL Server instance. SQL Server support 2 authentication modes Windows Authentication and Mixed Mode Authentication. Understand SQL Server Authentication Types and Modes on Decode ITeS.
  2. Enter the SA account password. Choose a strong password.
  3. Select windows account that you want to have sysadmin right

Step 19: Database Engine Configuration – Data Directories You Change system \ user \ backup directory as per disk configured on the server

Step 20: Database Engine Configuration – TempDB. TempDB is crucial for database performance. Microsoft adds the option to configure TempDB as per best practices or as per your requirements with the installation. This will ease up the DBA task after server installation.

Step 21: Database Engine Configuration – MaxDOP. I will suggest to leave it on default value and change it after proper DBA recommendation and application testing.

Step 22: Database Engine Configuration – Memory Configuration. Enter min and max server memory and accept ti use recommended values instead of default one. Same as above, Please configure it after DBA review and recommendation.

Step 23: Database Engine Configuration – Filestream. You can enable filestream in case required else leave it as it is.

Step 24: Analysis Services Configuration. Do not forget to add the user for admin privileges on SSAS.

Step 25: Integration Services – Master Mode, Leve on default no changes. Change only after proper testing and DBA review.

Step 26: Integration Services – Worker Mode, Leve on default no changes. Change only after proper testing and DBA review.

Step 27: Distributed Replay Controller – Add admin account

Step 28: Distributed Replay Controller – Mention Controller Name

Step 29: Accept R Services.

Step 30: Accept Python installation

Step 31: Rule checker based on feature configuration

Step 32: Setup is ready to install, You can review configuration in the current window. Click next to start the installation.

Step 33: Installation is completed.

Step 34: You can review setup installation logs and services installed.

SQL Server 2019 setup comes up with a long list of options. These are very helpful and tries to install setup with the most recommended and best practices configuration.

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: