SQL Saturday 589 Interview

Tweet SQL Saturday 589 with Power BI

Basic Data Modelling with DAX by Marco Pozzan on Parma’s Univeristy

Power BI: basic data modelling with DAX by Marco Pozzan from UGISS on Vimeo.

Cloud Conference 2016 My video on Power BI + Flow

My new video on Dax Fundamentals

SSRS Branding File Editor

A new feature of branding has been introduced for SSRS in SQL Server 2016. This feature is useful for customizing the SSRS Report Manager Portal with Display colors and a logo also can be added to the portal.

The tool is aviable on http://ssrsbrandingfileeditor.codeplex.com/

Performance Issues with Number of Virtual Log Files In SQL Server

Overview

Each transaction log file is made of smaller parts, known as virtual log files. The number of virtual log files per transaction log files is not fixed. Its size is determined dynamically when the transaction log file is executed. The number of virtual log files cannot be set or configured by the database administrator. If the auto-growth settings are not properly managed then, database can be forced to auto-grow that can cause serious performance issues. In the following section, we will discuss the causes and number of virtual log files can exist.

Impact of VLF on SQL Server Performance

In SQL Server, Transaction log files are set at 2MB at initial size. In addition, 10% of current size is the default growth value. At the time of creating SQL Server database, these options can be modified to accommodate the needs of database. The auto-growth option is optional which is turned on by default. SQL Server creates a database with unrestricted file growth. If the settings are not properly managed, then it will create an issue. Until, the auto-growth is finished, the server will stop all the processing. The auto-growth will take up the space that is not physically close to previous one due to physical organization of hard drive. It leads to physical fragmentation that causes the slower response in performance.

The Server should not have an excessive number of virtual log file inside the translation log. Large number of small virtual log file slows down the process of recovery, which database goes through on startup otherwise after restoring a backup. The threshold for significantly affecting the recovery performance appears to be around ten thousand virtual log files. When there are about hundred thousand virtual log files then, symptoms become significantly noticed.

Tips to Fix this Perfomance Issue:

  • One can determine the no. of VLFs in specific database by checking the no. of records that are returned as resulted of the executed DBCC command within the text by using DBCC LOGINFO
  • The number of virtual log files can be decreased by running the DBCC SHRINKFILE command.

Conclusion

In the discussion, performance issue with large number of Virtual Log Files in SQL Server Transaction is discussed. It provides guidance for users to have proper understanding about the virtual log file impact on SQL Server that results in slow down of the performance of the server.

Proficient Way For Giving and Removing Permissions in SQL Server

Overview

While working with SQL Server Database, users has the right to assign or remove permission, to allow which user can perform particular tasks on the database. Due to security reasons, only some of the default database users such as db_owner, db_datawriter, etc. are granted access to the database. However, some database needs to be accessed by other users at times. For this purpose, some commands can be used to grant or deny access in SQL Server. Through this page, we will learn the technique for giving and removing permissions in SQL Server.

SQL Server Commands

There are many commands supported by SQL Server, however in this case we will define only the three commands that can be used to give and remove access to the SQL Server users. They are as follows:-

i. GRANT– It is used to give users permission to perform some tasks on the SQL Server Database objects.

ii. DENY– It is used to deny any access to an user from performing certain tasks on database objects.

iii. REVOKE– It is used to remove grant or deny permission from the user that was earlier assigned to perform any tasks on the SQL database objects.

Giving Permissions in SQL Server

As stated in the previous paragraph, for giving permissions we will use GRANT command. The following syntax will be used for granting privileges on a table in SQL Server database:-

GRANT privileges ON object TO user;

Privileges: Some privileges that can be assigned to the user are as follows:

  1. SELECT Able to perform SELECT statements on table.
  2. INSERT Able to use INSERT statements on table
  3. UPDATE Able to use UPDATE statement on table
  4. DELETE Ability to perform DELETE operation on the table
  5. ALTERTo perform ALTER TABLE statements to modify table definition

Object: It denotes the name of the database object that needs to be given access. For example, if we want to grant access on a SQL Server table, object will be the table name.

User: It will define the name of the user to which we need to grant access over the object.

Some Examples:

  • GRANT INSERT, UPDATE ON Students TO John, Jessica; [Here, the command will allow John and Jessica to insert or update data in table Students]
  • GRANT SELECT ON Students TO public; [Here, we will grant only SELECT access on the Students Table to all users by giving access to public]

Removing Permissions in SQL Server

Similarly, for removing some or all permissions on a table in SQL Server database we will use REVOKE command on some or all the privileges. Privileges can be combination of SELECT, INSERT, UPDATE, DELETE or ALL. The following syntax will be used for revoking privileges on the SQL Server Table:

REVOKE privileges ON object FROM User;

Here, Privileges can be any of the privileges discussed earlier in GRANT section. Object will the name of the database object from where we are removing permissions. User will be the name of the users whose permissions that was already assigned will be removed.

Some Examples:

  • REVOKE DELETE ON Students FROM Alex; [Here, User Alex has been removed permission to perform delete operation on Students table]
  • REVOKE ALL on Students FROM Maria; [Here, it is used to remove ALL permissions i.e., SELECT, INSERT, UPDATE, DELETE & REFERENCES from Maria User in Students Table]

Denying Permissions in SQL Server

A DENY command can be used to deny the DELETE any access or privileges on a SQL Server database object.

Example: DENY UPDATE ON Students to Oliver; [Here, the statement will deny the UPDATE operation on Students Table by user Oliver.]

Conclusion

The content has been aimed to guide users of SQL Server database in giving or removing permissions in SQL Server database objects using some commands. Additionally, we can grant users permissions not only on objects, but also on other tasks like creating tables, views or stored procedures. Using these commands, we can give access to perform tasks on database by the desired users instead of using the default database roles.

Best Disaster Recovery Plans for SQL Server Database

Overview

SQL Server Disaster is a condition that causes data loss or any disturbance in SQL Server. There are various users, who face the similar problem but they do not know how to overcome from such problem. Therefore, in the below discussion there are some reasons for occurrence of disaster and different SQL Server disaster recovery plans.

Why you should prepare the blueprint to Prevent SQL Server Disater?

The disaster can be caused because of various reasons and some of them are mentioned below:

  • Natural disaster
  • Hardware failure
  • Power failure
  • Human error

Due to issues related to SQL Server disaster that cannot be prevented, it is important to have a good disaster recovery plan that will be discussed in the following section.

Best Disaster Recovery Plans for Database

SQL Server DRP (disaster recovery plan) is a way to overcome from data loss after a disaster. Disaster recovery plan should be planned in way to avoid disastrous data loss and incidents. SQL Server disaster recovery plan can be taken into account many factors such as data loss tolerance, data sensitivity, required availability, etc. The plan for disaster recovery are based on few solutions that are discussed below, every solution has its own benefit of implementing.

1. Failover Clustering

It is a concept in which SQL Server cases are installed on the shared storage. It offers a frame, which maintains the high-availability as well as disaster recovery scenarios of server applications. If the cluster node fails then the facilities that held on that node will automatically or manually transfer it to the other available node in that process, which is called failover. When SQL Server is terminated, then there is a short period of downtime.

2. Database Mirroring

It is way to increase the availability of SQL Server database. It preserves the two same copies of single database. These copies should be on diverse SQL Server instances. The two databases that form a relationship are known as database mirroring session. In it, one instance acts as principal server, whereas another is in the mode of standby that acts as mirror server. The two SQL Server instances, which perform in the mirroring environment, are called partners. The principal server sends the active portion of transaction log to mirror server. All the transactions are redone. There are two types of mirror servers, i.e. warm and hot. A hot mirror server has quick failover time without data loss because it has synchronized session. However, in warm mirror server, there is a possibility of data loss, as it does not have synchronized session.

3. Replication

It is used to copy the data and distribute the data from one SQL Server database to other. The stability is achieved by synchronization of data. There are many benefit of SQL Server database replication such as offline processing, redundancy, and load balancing. The load balancing permits the spreading of data to various SQL Servers and allocating the load query among those SQL Servers. The replication involves two components, i.e.

1. Publishers: Publishers are those who provide the data. In any replication, there are one or more publishers.

2. Subscribers: Subscribers are those who receive the data from publishers through replication. When the data publisher is modified, the data in subscribers is updated.

There are three types of replication that are supported by SQL Server.

1. Merge Replication: In replication, publisher and subscriber make changes independently on SQL Server database. The merge manager monitors the modifications on the subscriber and publisher, if required it changes the databases. In case of conflict, the predefined algorithm determinates the suitable data.

2. Snapshot Replication: In it, the publisher creates a snapshot of the whole data and makes it accessible for all subscribers.

3. Transactional Replication: It proceeds the replication of agents that monitors the modifications on publisher and transfers all these changes to subscribers.

4. Log Shipping

It is based on automatic sending of operational log backups from a primary SQL Server instance to one or many secondary SQL Server instances. The production server is a primary SQL Server instances whereas warm standby copy is the secondary SQL Server instance. There can also be a third SQL Server instance that acts as monitoring server. There are three main log shipping operations, i.e. copying transaction log backup to one or more secondary servers, creating the backup of transaction log on primary SQL Server, and restoring the backup of transaction log backup on secondary server.

5. Backup and Restore

It is a basic measure that should be taken to keep the data safe. It involves two main concepts, i.e. backup of SQL Server data and restoring of SQL Server data. The data that is backed up is moved to neutral off-site location whereas the restore is tested to assure the integrity of data. There are various types of backups in SQL Server such as partial backup, transaction log backup, differential backup and full back up. The backup plan defines the type of backup and its frequency, way to test the backups, and storage of backup media. The restore plans define the one who is responsible for restoration of data and a way to restore the data to meet the availability and aims of data loss.

Conclusion

In the above discussion, various SQL Server Disaster recovery plans are discussed. Every recovery plan has its own benefit that the user can implement according to the requirement. In case, if you did not chosen any disaster recovery plan for your database, then you can also go with some helpful database recovery tool availble in market like SysTools SQL recovery tool which can be helpful and recover the database from corrupt state.

Know How to Fix Microsoft SQL Server Error 18452

Problem

When the user tries to login, they may receives an error message “Login failed. The login is from untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)” which is a common problem while working on SQL Server.

SQL Server Error 18452 Solutions

There are various workarounds, which helps user to remove the SQL Server Error 18452. Some common fixes are discussed below, which helps to resolve occurrence of error.

– The server accepts only Windows Authentication but the login may be of SQL Server

For the successful login in SQL Server, user first needs to modify the sign-in procedure by login their SQL Server account with Windows Authentication mode for this follow the steps given below:

1. Open the SQL Server Management Studio >> Select Authentication mode >> Click on connect button to login in SQL Server.

2. Now, right click on Object Explorer >> Properties.

3. Choose the security option from server properties dialog box >> Choose SQL Server and Windows Authentication mode, which is at right-panel of window >> Ok.

4. Select Security, then choose logins and right click on SA >> Properties.

5. Now enter the credentials in login properties dialog box.

6. Select the status option, in a login mode choose the enable option.

Now enable the mixed authentication mode by using the steps below:

1. Right-click on the SQL Server instance >> Properties

2. Select the security option, which is on left pane.

3. Choose the SQL Server and Authentication mode option >> OK.

– Trying to sign-in with SQL Server Authentication but unable to login as it does not be there on SQL Server

For SQL Server login, confirmation is required for login user. If user is not having any login details then, they are supposed to make a new login-in.

– Sign-in uses Windows Authentication however according to Windows principal login is unrecognized

Windows principal login is not recognized means that Windows cannot validate login. It may be due to untrusted domain. To overcome from such issue, user can sign-in into an authentic domain.

– Enabling the SQL Server login

To fix Microsoft SQL Server error 18452, user needs to enable the SQL Server by following the steps:

1. Sign-in with Windows Authentication mode to SQL Server

2. Change the properties of SQL Server

3. Select the enable the SA account logon.

– Sign-in is correct but the server access has failed

Windows Firewall has not permitted SQL Server access. To resolve this, user needs to allow the configuration on Windows firewall for accessing the SQL Server by following the steps given below:

1. Open the Control Panel in your system

2. Select the System and Security option >> Windows Firewall

3. Choose an option at left-panel “allow a program or feature through Windows Firewall”.

4. At bottom of the window, choose an option allow another program.

5. Now, Click on the browse option

6. Choose the sqlserver.exe >> Open >> Add

Now, user can easily access the SQL Server through windows Firewall.

Conclusion

In the above blog, there are various workarounds discussed to resolve the Microsoft SQL Server Error 18452. By utilizing these methods, user can easily overcome and can continue their work afterwards.

Switch to our mobile site