Category: ms Sql Server

MS SQL Server – HA Technologies ”; Previous Next High Availability (HA) is the solutionprocesstechnology to make the applicationdatabase available 24×7 under either planned or un-planned outages. Mainly, there are five options in MS SQL Server to achievesetup high availability solution for the databases. Replication The source data will be copied to destination through replication agents (jobs). Object level technology. Terminology Publisher is source server. Distributor is optional and stores replicated data for the subscriber. Subscriber is the destination server. Log Shipping The source data will be copied to destination through Transaction Log backup jobs. Database level technology. Terminology Primary server is source server. Secondary server is destination server. Monitor server is optional and will be monitored by log shipping status. Mirroring The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology. Terminology Principal server is source server. Mirror server is destination server. Witness server is optional and used to make automatic failover. Clustering The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. Windows Clustering setup is required with shared storage. Terminology Active node is where SQL Services are running. Passive node is where SQL Services are not running. AlwaysON Availability Groups The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage. Terminology Primary replica is source server. Secondary replica is destination server. Following are the steps to configure HA technology (Mirroring and Log shipping) except Clustering, AlwaysON Availability groups and Replication. Step 1 − Take one full and one T-log backup of source database. Example To configure mirroringlog shipping for the database ”TestDB” in ”TESTINSTANCE” as primary and ”DEVINSTANCE” as secondary SQL Servers, write the following query to take full and T-log backups on Source (TESTINSTANCE) server. Connect to ”TESTINSTANCE” SQL Server and open new query and write the following code and execute as shown in the following screenshot. Backup database TestDB to disk = ”D:testdb_full.bak” GO Backup log TestDB to disk = ”D:testdb_log.trn” Step 2 − Copy the backup files to destination server. In this case, we have only one physical server and two SQL Servers Instances installed, hence there is no need to copy, but if two SQL Server instances are in different physical server, we need to copy the following two files to any location of the secondary server where ”DEVINSTANCE” instance is installed. Step 3 − Restore the database with backup files in destination server with ”norecovery” option. Example Connect to ”DEVINSTANCE” SQL Server and open New Query. Write the following code to restore the database with the name ”TestDB” which is the same name of primary database (”TestDB”) for database mirroring. However, we can provide different name for log shipping configuration. In this case, let’s use ”TestDB” database name. Use ”norecovery” option for two (full and t-log backup files) restores. Restore database TestDB from disk = ”D:TestDB_full.bak” with move ”TestDB” to ”D:DATATestDB_DR.mdf”, move ”TestDB_log” to ”D:DATATestDB_log_DR.ldf”, norecovery GO Restore database TestDB from disk = ”D:TestDB_log.trn” with norecovery Refresh the databases folder in ”DEVINSTANCE” server to see restored database ”TestDB” with restoring status as shown in the following snapshot. Step 4 − Configure the HA (Log shipping, Mirroring) as per your requirement as shown in the following snapshot. Example Right-click on ”TestDB” database of ”TESTINSTANCE” SQL Server which is primary and click Properties. The following screen will appear. Step 5 − Select the option called either ”Mirroring” or ”Transaction Log Shipping” which are in red color box as shown in the above screen as per your requirement and follow the wizard steps guided by system itself to complete configuration. Print Page Previous Next Advertisements ”;

MS SQL Server – Assign Permissions ”; Previous Next Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server. To assign permissions either of the following two methods can be used. Method 1 – Using T-SQL Syntax Use <database name> Grant <permission name> on <object name> to <usernameprinciple> Example To assign select permission to a user called ”TestUser” on object called ”TestTable” in ”TestDB” database, run the following query. USE TestDB GO Grant select on TestTable to TestUser Method 2 – Using SSMS (SQL Server Management Studio) Step 1 − Connect to instance and expand folders as shown in the following snapshot. Step 2 − Right-click on TestUser and click Properties. The following screen appears. Step 3 Click Search and select specific options. Click Object types, select tables and click browse. Select ”TestTable” and click OK. The following screen appears. Step 4 Select checkbox for Grant column under Select permission and click OK as shown in the above snapshot. Step 5 Select permission on ”TestTable” of TestDB database granted to ”TestUser”. Click OK. Print Page Previous Next Advertisements ”;

MS SQL Server – Integration Services ”; Previous Next This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are the environments to develop packages. SSIS Basic Architecture Solution (Collection of projects) —> Project (Collection of packages) —> Package (Collection of tasks for ETL and admin operations) Under Package, the following components are available − Control Flow (Containers and Tasks) Data Flow (Source, Transformations, Destinations) Event Handler (Sending of messages, Emails) Package Explorer (A single view for all in package) Parameters (User interaction) Following are the steps to open BIDSSSDT. Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen appears. Step 2 − The above screen shows SSDT has opened. Go to file at the top left corner in the above image and click New. Select project and the following screen opens. Step 3 − Select Integration Services under Business Intelligence on the top left corner in the above screen to get the following screen. Step 4 − In the above screen, select either Integration Services Project or Integration Services Import Project Wizard based on your requirement to developcreate the package. Print Page Previous Next Advertisements ”;

MS SQL Server – Create Database ”; Previous Next Database is a collection of objects such as table, view, stored procedure, function, trigger, etc. In MS SQL Server, two types of databases are available. System databases User Databases System Databases System databases are created automatically when we install MS SQL Server. Following is a list of system databases − Master Model MSDB Tempdb Resource (Introduced in 2005 version) Distribution (It’s for Replication feature only) User Databases User databases are created by users (Administrators, developers, and testers who have access to create databases). Following methods are used to create user database. Method 1 – Using T-SQL Script or Restore Database Following is the basic syntax for creating database in MS SQL Server. Create database <yourdatabasename> OR Restore Database <Your database name> from disk = ”<Backup file location &plus; file name> Example To create database called ‘Testdb’, run the following query. Create database Testdb OR Restore database Testdb from disk = ”D:BackupTestdb_full_backup.bak” Note − D:backup is location of backup file and Testdb_full_backup.bak is the backup file name Method 2 – Using SQL Server Management Studio Connect to SQL Server instance and right-click on the databases folder. Click on new database and the following screen will appear. Enter the database name field with your database name (example: to create database with the name ‘Testdb’) and click OK. Testdb database will be created as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Monitor Database ”; Previous Next Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc. SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc. Database status can be checked whether it is online or in any other state as shown in the following snapshot. As per the above screen, all databases are in ”Online” status. If any database is in any other state, then that state will be shown as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Management Studio ”; Previous Next SQL Server Management Studio is a workstation componentclient tool that will be installed if we select workstation component in installation steps. This allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line. In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc. The following methods are used to open SQL Server Management Studio. First Method Start → All Programs → MS SQL Server 2012 → SQL Server Management Studio Second Method Go to Run and type SQLWB (For 2005 Version) SSMS (For 2008 and Later Versions). Then click Enter. SQL Server Management Studio will be open up as shown in the following snapshot in either of the above method. Print Page Previous Next Advertisements ”;

MS SQL Server – Creating Backups ”; Previous Next Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log. Backup database can be done using either of the following two methods. Method 1 – Using T-SQL Full Type Backup database <Your database name> to disk = ”<Backup file location + file name>” Differential Type Backup database <Your database name> to disk = ”<Backup file location + file name>” with differential Log Type Backup log <Your database name> to disk = ”<Backup file location + file name>” Example The following command is used for full backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_Full.bak” Backup database TestDB to disk = ”D:TestDB_Full.bak” The following command is used for differential backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_diff.bak” Backup database TestDB to disk = ”D:TestDB_diff.bak” with differential The following command is used for Log backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_log.trn” Backup log TestDB to disk = ”D:TestDB_log.trn” Method 2 – Using SSMS (SQL SERVER Management Studio) Step 1 − Connect to database instance named ”TESTINSTANCE” and expand databases folder as shown in the following snapshot. Step 2 − Right-click on ”TestDB” database and select tasks. Click Backup and the following screen will appear. Step 3 − Select backup type (Fulldifflog) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen. Step 4 − Click OK to create ”TestDB” database full backup as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Installation ”; Previous Next SQL Server supports two types of installation − Standalone Cluster based Checks Check RDP access for the server. Check OS bit, IP, domain of server. Check if your account is in admin group to run setup.exe file. Software location. Requirements Which version, edition, SP and hotfix if any. Service accounts for database engine, agent, SSAS, SSIS, SSRS, if any. Named instance name if any. Location for binaries, system, user databases. Authentication mode. Collation setting. List of features. Pre-requisites for 2005 Setup support files. .net framework 2.0. SQL Server native client. Pre-requisites for 2008&2008R2 Setup support files. .net framework 3.5 SP1. SQL Server native client. Windows installer 4.5/later version. Pre-requisites for 2012&2014 Setup support files. .net framework 4.0. SQL Server native client. Windows installer 4.5/later version. Windows PowerShell 2.0. Installation Steps Step 1 − Download the Evaluation Edition from https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019 Once the software is downloaded, the following files will be available based on your download (32 or 64 bit) option. ENUx86SQLFULL_x86_ENU_Core.box ENUx86SQLFULL_x86_ENU_Install.exe ENUx86SQLFULL_x86_ENU_Lang.box OR ENUx86SQLFULL_x64_ENU_Core.box ENUx86SQLFULL_x64_ENU_Install.exe ENUx86SQLFULL_x64_ENU_Lang.box Note − X86 (32 bit) and X64 (64 bit) Step 2 − Double-click the “SQLFULL_x86_ENU_Install.exe” or “SQLFULL_x64_ENU_Install.exe”, it will extract the required files for installation in the“SQLFULL_x86_ENU” or “SQLFULL_x86_ENU” folder respectively. Step 3 − Click the “SQLFULL_x86_ENU” or “SQLFULL_x64_ENU_Install.exe” folder and double-click “SETUP” application. For understanding, here we have used SQLFULL_x64_ENU_Install.exe software. Step 4 − Once we click on ”setup” application, the following screen will open. Step 5 − Click Installation which is on the left side of the above screen. Step 6 − Click the first option of the right side seen on the above screen. The following screen will open. Step 7 − Click OK and the following screen pops up. Step 8 − Click Next to get the following screen. Step 9 − Make sure to check the product key selection and click Next. Step 10 − Select the checkbox to accept the license option and click Next. Step 11 − Select SQL Server feature installation option and click Next. Step 12 − Select Database engine services checkbox and click Next. Step 13 − Enter the named instance (here I used TestInstance) and click Next. Step 14 − Click Next on the above screen and the following screen appears. Step 15 − Select service account names and start-up types for the above listed services and click Collation. Step 16 − Make sure the correct collation selection is checked and click Next. Step 17 − Make sure authentication mode selection and administrators are checked and click Data Directories. Step 18 − Make sure to select the above directory locations and click Next. The following screen appears. Step 19 − Click Next on the above screen. Step 20 − Click Next on the above screen to the get the following screen. Step 21 − Make sure to check the above selection correctly and click Install. Installation is successful as shown in the above screen. Click Close to finish. Print Page Previous Next Advertisements ”;