Creating a Calendar Table in SQL Server

Introduction

A calendar table is useful for any SQL Server database, as it allows for easy querying and reporting of date-based data. Reporting often requires aggregating or returning data based on date attributes such as weekdays, holidays, quarters, or time of year. Creating a calendar table can save time, improve performance, and increase consistency in data. In this guide, we will walk through the process of creating a calendar table in SQL Server, including the necessary code and best practices for maintaining and using the table.

You can change some of these details to experiment on your own.

Why Use a Calendar Table?

There are several benefits to using a calendar table in your SQL queries. First and foremost, a calendar table can greatly simplify date-based queries. Without a calendar table, you may have to use complex logic to extract the year, month, day, etc. from a date field. With a calendar table, you can simply join to it and use the pre-calculated date fields.

Another benefit of a calendar table is that it can be used to quickly fill in missing dates in your data. If you have a sales table that only has entries for dates when there were actual sales, a calendar table can be used to fill in the missing dates and show “zero” sales for those dates. This can be particularly useful when creating graphs or charts that need to have a consistent X-axis.

Finally, a calendar table can be used to quickly apply filters to your data based on dates. For example, you can use a calendar table to easily filter your data to only show records from the current month, or the last 30 days.

Step 1: Create the Table Structure

The first step in creating a calendar table is to create the table structure. We will be using the following code to create the table. You can also include other columns such as quarter or fiscal year, depending on your specific requirements.

CREATE TABLE tblCalendar(
       [Date] Date NOT NULL,
       [Day] char(10) NOT NULL,
       [DayOfWeek] tinyint NOT NULL,
       [DayOfMonth] tinyint NOT NULL,
       [DayOfYear] smallint NOT NULL,
       [PreviousDay] date NOT NULL,
       [NextDay] date NOT NULL,
       [WeekOfYear] tinyint NOT NULL,
       [Month] char(10) NOT NULL,
       [MonthOfYear] tinyint NOT NULL,
       [QuarterOfYear] tinyint NOT NULL,
       [Year] int NOT NULL,
       [IsWeekend] bit NOT NULL,
    )
 
ALTER TABLE tblCalendar
ADD CONSTRAINT PK_CalendarDate PRIMARY KEY (Date); 

You should add a primary key to ensure the data is unique and can be easily searched.

Step 2: Populate the Table with Data

Once the table structure is created, it’s time to populate it with data. You can use a series of INSERT statements to add data for a specific range of dates. In the below example I am inserting 365 days of data starting from 1st Jan’23.

DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = '2023-01-01' --Put the start date as per your requirement
SET @EndDate = DATEADD(d, 365, @StartDate) --Change the no. of days as needed
WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO tblCalendar values
             (
                   @StartDate,
                              CONVERT(CHAR(10), DATENAME(WEEKDAY, @StartDate)),
                              CONVERT(Tinyint,DATEPART(WEEKDAY, @StartDate)),
                              CONVERT(Tinyint,DATEPART(DAY, @StartDate)),
                              CONVERT(smallint, DATEPART(DAYOFYEAR, @StartDate)),
                              DATEADD(day, -1, CONVERT(DATE, @StartDate)),
                              DATEADD(day, 1, CONVERT(DATE, @StartDate)),
                              CONVERT(tinyint, DATEPART(WEEK,@StartDate)),
                              CONVERT(CHAR(10), DATENAME(MONTH, @StartDate)),
                              CONVERT(TINYINT, DATEPART(MONTH, @StartDate)),
                              CONVERT(TINYINT, DATEPART(QUARTER,@StartDate)),
                              CONVERT(INT, DATEPART(YEAR,@StartDate)),
                              CASE 
                                   WHEN CONVERT(Tinyint,DATEPART(WEEKDAY, @StartDate)) in (1,7)
                                   THEN 1
                                   ELSE 0 
                                 END
             )
             
             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Sample Output:

Calendar Table In SQL Server

Next Steps:

To ensure that the calendar table is as efficient as possible, it’s important to set up appropriate indexes and constraints once the table grows considerably large.

Conclusion:

Creating a calendar table in SQL Server can greatly improve performance and simplify your code when joining data on date-related attributes in reporting, analytics, or OLTP use cases. These tables also make it easier for reporting engines to consume the data and increase maintainability by keeping a single copy of calendar data in one place, reducing the risk of coding errors when working with complex date calculations

You can also refer the following link for more idea https://www.sqlshack.com/designing-a-calendar-table/

You may also like the below articles:

Script To List SQL Agent Jobs and Schedules

Script to Change SQL Agent Job Schedule

Script to Find Agent Job in Execution

 

 

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/creating-calendar-table-sql-server/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

What is SQL Server

Introduction to SQL Server

In this blog post, we will discuss What is SQL Server. You will learn its features, and how it manages and stores large amounts of data for organizations of all sizes.

What is SQL Server?

First thing to know is Microsoft Developed SQL Server and hence it is also called as MS SQL Server. It is a powerful RDBMS that allows organizations to store, manage, and retrieve data. Moreover SQL Server can handle both structured and unstructured data and work with a wide range of data types. SQL Server manages data for a variety of applications, including enterprise resource planning (ERP), customer relationship management (CRM), and data warehousing.

Features of SQL Server

SQL Server offers a wide range of features that make it a powerful tool for managing and storing data. Some of the key features of SQL Server include:

  • Scalability: SQL Server can scale up or down to meet the needs of any organization, from small businesses to large enterprises.
  • High availability: SQL Server can be configured for high availability to ensure that data is always available and that there is minimal downtime.
  • Security: SQL Server includes a variety of security features to help protect data from unauthorized access.
  • Business Intelligence: SQL Server equips a variety of business intelligence (BI) tools that allows users to analyze and visualize data.
  • Cloud-ready: SQL Server’s availability in both on-premise and cloud-based versions makes it easy to deploy and manage in any environment.

SQL Server usage

SQL Server can manage and store data for a wide range of applications. Some of the most common uses of SQL Server include:

  • Data warehousing: SQL Server stores and manages data for data warehousing applications. This allows organizations to analyze and report on large amounts of data.
  • Business Intelligence: SQL Server also stores and manages data for BI applications. This allows organizations to analyze and visualize data for decision making.
  • Website and Application: Many websites and applications uses SQL Server serves as a backend, it also stores and retrieves data quickly and efficiently.

In conclusion, SQL Server is a powerful RDBMS which stores, manages, and retrieves data for a wide range of applications. As can be seen, with its wide range of features and scalability, it is well-suited for organizations of all sizes. If you’re looking for a powerful and flexible tool for managing your data, SQL Server is a great option to consider.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/what-is-sql-server/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

SQL Server Orphaned Files: Causes and Consequences

SQL Server orphaned files are a common issue faced by database administrators. These files are no longer in use by the SQL Server database but still exist on the file system. In this blog post, we will discuss the causes of SQL Server orphaned files and the problems they can cause.

Causes of Orphaned Files

There are several reasons why orphaned files can occur in a SQL Server environment. Some of the common causes include:

  • Detached databases: When a database is detached from the SQL Server, the files are not automatically removed, leaving behind orphaned files. For example, if a database is detached and the files are not deleted, they will be considered as orphaned files.
  • Improperly deleted Databases: This is often overlooked. Whenever you do database decommission, as per best practice first you keep it  offline for a week or so. Once you do not hear anything you go ahead and delete it.

Here is the catch. If you delete the database files while the database is offline, SQL Server do not remove the files automatically.

Let me give you a demo. In the below example I have created a database “AA_Decom”.

Offline Database deletion keeps the physical files

SQL Server Orphaned Files

Now let me take the database offline and then delete it.

SQL Server Orphaned Files

 

Right Click on the database and Delete:

SQL Server Orphaned Files

Let’s Verify if the database and Files are visible to SQL Server. The below screen shot shows they are no longer available.

SQL Server Orphaned Files

However the below screen shot shows the physical files still exists in the data and log file path. This is one of the main reasons of having SQL Server Orphaned files.

SQL Server Orphaned Files

SQL Server Orphaned Files

  • Upgrading or Migration: During the process of upgrading or migrating to a new version of SQL Server, if the files are not properly transferred or handled, it can lead to orphaned files.

Consequences of Orphaned Files

Orphaned files in SQL Server can lead to a number of negative consequences. Some examples of the consequences of orphaned files include:

  • Lack of Disk Space: Orphaned files can take up valuable disk space on the server, which can lead to disk space issues. For example, if the disk space is full and the SQL Server cannot write to the transaction log, it can cause the database to become unavailable.
  • Difficulty in Identifying and Removing Unnecessary Files: These files can also cause confusion when trying to identify which files are in use and which are not, making it difficult to properly manage and maintain the database. For example, if the DBA has to go through hundreds of files to identify which are in use and which are not, it can take a lot of time and resources.
  • Security Risks: Orphaned files can also pose a security risk. For example, if an orphaned file contains sensitive information, it could be accessed by unauthorized users.

It’s important to keep an eye on the orphaned files and take necessary actions to remove them, in order to avoid these consequences. Let me know if you have observed any other scenarios by putting your comments below.

You may also like to go through below articles:

Top 5 Tips for Speeding Up SQL Server Backups

Tips to Improve SQL Database Restore Performance

How to Force Manual Failover AlwaysOn Group

 

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/sql-server-orphaned-files-causes-and-consequences/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Junior SQL DBA Interview Questions Answers -Part3

Hello there! As the title depicts this article is 3rd in the series of Junior SQL DBA Interview Questions Answers. Please go through the first two articles Junior SQL DBA Interview Q&A and Junior SQL DBA Q&A – Part2 .

By the way though it is about Junior SQL DBA Interview, you may get these questions for mid to senior level DBAs as well. In the first place Interviewer generally starts with very basic questions.

1. What are the different types of SQL Server database Backups?

Ans: SQL Server supports the following backup types:

  1. Full
  2. Differential
  3. Transaction log
  4. Tail log
  5. Copy Only
  6. File and Filegroup backup
  7. Partial backup

Full Backup: A full database backup backs up the whole database. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. This includes part of the transaction log required to fully recover the database.

Differential Backup: Backup all changed extents since last Full backup. Basically it contains all changes since the last full backup.

Transaction log Backup: Also usually called as Log backup, backups all transactions in the database transaction logs since the last log backup. Important to realize that the transaction log backups are incremental and differential backups are cumulative in nature.

Tail log Backup: A tail-log backup captures all log records that have not yet been backed up to prevent work loss.

Copy Only Backup: A copy-only backup is a special type of SQL Server backup that is independent of the sequence of conventional backups. In other words it does not change the differential base and hence does not affects the database restore sequence.

File Backup: This allows you to backup each file independently instead of having to backup the entire database. This is useful when you have a very large database.

Filegroup Backup: This type of backups allows you to backup all files that are in a particular filegroup.

Partial Backup: This allows you to backup only Read-Write Filegroups and exclude Read-Only Filegroups. This is useful for very large database.

2. How can you verify your database backups and make sure they are restorable?

Ans: Using RESTORE VERIFYONLY command we can verify.

RESTORE VERIFYONLY FROM DISK = ‘C:\AdventureWorks.bak’    — Backup path including Filename GO Output: SQL DBA Interview
3. Is it possible to backup RESOURCE database?

Ans: NO! it is not possible to backup RESOURCE database using conventional methods. However you can take physical file level backup of .mdf and .ldf files.

4. What is the password policy of SQL Server?

Ans: SQL Server is able to utilize the password policy mechanisms of Windows. SQL Server login authentication uses this password policy.

SQL Server can apply the same complexity and expiration policies used in Windows to passwords used inside SQL Server.

5. What is  SQL Server Login?

Ans: A person or application can connect to a SQL Server instance using a login, which is a user account used for authentication.

6. What is a database user in SQL Server?

Ans: To connect to the database, a user account called a database user is created at the database level in SQL Server. A login created at the server level is typically mapped to a database user.

7. What is a schema in SQL Server?

Ans: A schema in SQL Server is a collection of objects, such as tables and views, that are owned by a specific user. Schemas can be used to group objects together and to provide an additional level of security.

8. What is a database role in SQL Server?

Ans: A specific set of permissions within a particular database is granted to a group of users known as a database role in SQL Server. You can use database roles to simplify the management of permissions and enhance security. SQL Server has fixed database-level roles built in, and you can create additional roles as needed.

9. What is SQL Server role?

Ans: SQL Server level roles lets group user logins together and manage server-level permissions. Server-level roles help manage permissions for the entire SQL Server instance. SQL Server provides several built in server roles, but you should add your own specific roles if possible. You can’t change fixed server roles.

10. What is the difference between SQL Server login and user?

Ans:

Microsoft SQL Server uses logins to authenticate users to the server. These logins function as security principals that connect users to the server and associate them with server-level roles that allow certain activities.

A user is a security principal that authenticates access to a database. It associates with a database-level role that grants permission for specific activities within the database.

In summary, logins authenticate users at the server level and users authenticate at the database level.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/sql-dba-interview-part3/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Tips to Improve SQL Database Restore Performance

Introduction:

If you’re a database administrator, you know that restoring a database from a backup can be a time-consuming process. In the last article Top 5 Tips To Speed Up SQL Server Backups we discussed about tips to improve the SQL Database Backup Performance.

In this blog post, we’ll explore the top tips for making SQL database restores faster. These tips include using faster hardware, using multiple threads, using compression, using partial restores and more will help to improve SQL Database Restore Performance.

Use Instant File Initialization:

There are ways to optimize the process of creating new files for a database during a restore, particularly when restoring for the first time or using the WITH MOVE option. This feature, available in SQL Server 2005 and above, allows the operating system (which must be Windows XP or newer or Windows Server 2003 or newer) to quickly initialize the new files. To utilize this feature, the account that SQL Server is running under must have the appropriate security settings, such as being a member of the Windows Administrator group on the server or having the Perform Volume Maintenance Tasks security policy.

Use Multiple Files:

If you have multiple disk drives you can radically improve the speed of backups, and going the other way, restores. You do this by splitting the backup up amongst the multiple drives by using more than one backup file. It will require syntax changes to both your backup and your restore commands. This is directly related to throwing hardware at the problem, but instead of concentrating on faster disks, you’re concentrating on more disks.

Use multiple threads:

Another way to speed up database restores is to use multiple threads. This allows the restore process to run concurrently on multiple CPU cores, which can significantly reduce the overall restore time. To use multiple threads in SQL Server, you can use the WITH (MAXDOP) option in the RESTORE DATABASE statement. For example:

RESTORE DATABASE mydatabase
FROM mybackup
WITH (MAXDOP = 4)

Use compression:

Compressing the backup file before performing the restore can also help reduce the restore time. SQL Server supports both data compression and backup compression, which can significantly reduce the size of the backup file. To use compression in SQL Server, you can use the WITH COMPRESSION option in the BACKUP DATABASE statement. For example:

BACKUP DATABASE mydatabase
TO mybackup
WITH COMPRESSION

Use faster hardware:

One of the most obvious ways to speed up database restores is to use faster hardware. This could mean upgrading your server’s CPU, adding more RAM, or using faster hard drives or solid-state drives (SSDs). If you’re restoring a large database, these hardware upgrades can make a significant difference in the restore time.

Use a faster network connection:

If you’re restoring a database from a remote location, the speed of your network connection can impact the restore time. If possible, use a faster network connection or a direct connection to the server to reduce the restore time.

Recovery:

A significant portion of the restore process involves transferring data from the backup file to the data files, but there is one additional step at the end called recovery. During recovery, any transactions that were completed during the backup are applied to the data, while those that were not completed are rolled back. The amount of time this takes can vary significantly depending on the size and number of transactions. The Recovery Interval setting in SQL Server determines the frequency of checkpoints, with a default value of zero meaning that checkpoints occur at regular intervals determined by the SQL server. Changing this value to a higher number will result in more transactions occurring before a checkpoint, which can extend the recovery time. However, unless frequent checkpoints are causing performance issues, it is generally recommended to leave the Recovery Interval set to zero for faster recovery.

Conclusion:

Restores can be intimidating and stressful, but implementing the best practices outlined in this article can help reduce anxiety and improve the speed of the restore process. It is important to regularly practice restore operations to ensure that you are prepared in case of a real disaster. Lack of familiarity with the restore process is a common issue that can cause significant difficulties. By following these tips and regularly practicing restores, you can minimize the risk of encountering problems during a real restore operation.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/improve-sql-database-restore-performance/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Top 5 Tips for Speeding Up SQL Server Backups

Introduction to SQL Server Backup Performance

Backing up your SQL Server databases is an essential part of any database maintenance plan. However, backups can also be a time-consuming process, especially for large databases. In this article, we will go over 10 proven strategies for speeding up SQL Server backups. These techniques can help you optimize your backup process and save time.

1. Use Faster Storage

One of the biggest factors that can affect backup performance is the speed of the storage where the backup is being saved. If you are using a slow hard drive or network share to store your backups, it will take longer to complete the backup process. To improve performance, consider using faster storage such as a solid-state drive (SSD) or a high-speed network attached storage (NAS) device.

2. Compress the Backup

Another way to speed up backups is to compress the backup file. SQL Server supports several different types of compression, including data compression and backup compression. Data compression can reduce the size of the data in the database, which can also improve backup performance. Backup compression compresses the backup file itself, which can reduce the amount of time it takes to write the backup to storage.

3. Use Multiple Backup Files

If you are backing up to a single file, one thread is assigned that can be the bottleneck for your backups. To improve performance, you can use multiple backup file in parallel. In this case multiple threads will be used.

The below example code creates Full backup across four disks:

BACKUP DATABASE AdventureWorks
TO DISK=N'E:\SQL Backups\AdventureWorks\AdventureWorks_Full_1__20231001.bak',
DISK=N'F:\SQL Backups\AdventureWorks\AdventureWorks_Full_2__20231001.bak',
DISK=N'G:\SQL Backups\AdventureWorks\AdventureWorks_Full_3__20231001.bak',
DISK=N'H:\SQL Backups\AdventureWorks\AdventureWorks_Full_4__20231001.bak'

4. Schedule Backups During Off-Peak Hours

If your database is in use during the day, the backup process can slow down due to increased activity on the server. To improve performance, consider scheduling backups to run during off-peak hours when there is less activity on the server.

5. Use Partial Backups

A partial backup is a backup of one or more filegroups or files in a database. This can be a faster option than a full backup, especially if you only need to back up a small portion of the database. However, it is important to note that you will need to restore the partial backup in addition to any other backups to restore the entire database.

Example Backup Script

Here is an example T-SQL script that demonstrates how to use some of these techniques to improve the performance of a database backup:

-- Set the backup compression level
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks_Backup.bak'
WITH COMPRESSION, COPY_ONLY;

This script creates a compressed, COPY_ONLY backup of the AdventureWorks database and saves it to the specified file path. By using the COMPRESSION and COPY_ONLY options, we can create a faster, more efficient backup.

The COPY_ONLY option allows you to create a backup without affecting the database’s transaction log. This can be useful if you want to create a “point-in-time” backup without disrupting the log backup chain. By using the COPY_ONLY option, you can create a backup more quickly because the transaction log does not need to be backed up as well.

Conclusion

By following these tips and techniques, you can significantly improve the performance of your SQL Server database backups. Whether you are using faster storage, compression, multiple backup devices, or the COPY_ONLY option, there are many ways to optimize your backup process and save time.

You may also want to check Tips to Improve SQL Database Restore Performance

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/speeding-sql-server-backups/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

What and How to Enable 3GB Switch

Hello there! In this article we are going to learn what is /3GB switch, enabling 3GB switch and verifying 3GB switch. First of all let me tell you in brief why you need to know about 32 bit application configurations.

Sooner or later you will encounter one of the legacy systems (e.g. SQL Server 2005 32 bit on Window Server 2003) having some weird issues or someone may reach out to you for help. Recently I got a question from one of the DBAs on how to enable 3GB switch as he/she never worked on such environment.

What is 3GB Switch

/3GB switch changes the first 4 gigs allocation. To put it differently,  without the switch, 2 gigs are allocated to the OS, and 2 gigs are allocated to applications. With the /3GB switch, application can utilize 3GB memory while the OS can use only the remaining 1 gig. This switch only affects the first 4 gigs of RAM. The switch is there or need to be added in boot.ini file.

How to Enable 3GB Switch

The below screen shots are from Window Server 2003 Standard Edition.
Right click on “My Computer” > Properties

Enabling 3GB Switch

Click on Advanced

Enabling 3GB Switch

Under “Startup and Recovery” click the “Settings” button.

Enabling 3GB Switch

Click On “Edit”

Enabling 3GB Switch

This will open the boot.ini file in notepad. Add /3GB in the last line as shown in the following screen shot and save the file.

Enabling 3gb switch

Not to mention, If the switch is enabled, you will find it (/3GB) already in the boot.ini file. This is how you can verify /3GB switch.

Hope it helps. Please let me know by putting your comments below. You may be interested in the below articles as well:

SQL Agent Job Schedule changed automatically

How to handle OS Patching In SQL Server Always On

Database restore stuck at 100 Percent

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/enabling-3gb-switch/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

SQL Server TCP and UDP Ports Explained

We all know that that Microsoft SQL Server uses many different ports to access various components of the product. In this article we are going to discuss about TCP and UDP ports.

To begin with, Ports are nothing but communication channel to manage system messaging among several different applications.

TCP and UDP Ports:

TCP stands for Transmission Control Protocol. The computers (Servers) connected to the Internet use TCP/IP to communicate with each other. In other words TCP/IP is a connection-based protocol. I.e. The sender sends numbered packets across the wire and the receiver checks and makes sure it got the correct packet.

Hence TCP/IP protocol is more safe and reliable. However it puts a higher load on the computer as it has to monitor the connection and the data going across it.

UDP stands for User Datagram Protocol. UDP protocol prefers speed over reliability. That is to say UDP does not connect directly to the receiving computer like TCP does, but rather sends the data out and relies on the devices in between the sending computer and the receiving computer to get the data where it is supposed to go properly.

You can think of the postal dept. as a practical  example. The sender place mail in the mailbox and hope the Postal Service will deliver it to the proper destination address. Most of the time it works but sometimes it fails to deliver.

What are the  SQL Server TCP and UDP ports?

The default instance of the SQL Server Database Engine listens on TCP port 1433. The named instances uses dynamic port. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port and open that particular port in firewall.

1433 port is also the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

The SQL Server Browser service listens on UDP port 1434 for incoming connections for named instance. The service then responds to the client with the TCP port number for the requested named instance.

Point often overlooked is TCP port 1434 (Yes, you read that correct) is the default SQL port for the Dedicated Admin Connection.

TCP port 2383 is the default port for SQL Server Analysis Services.

Refer the Microsoft link All SQL Server Ports for more details.

 

 

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/tcp-udp-ports/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Junior SQL DBA Interview Question Answers -Part2

Hello there! This article is about Junior SQL DBA Interview Q&A. Please go through the first article of this Interview question answers series Junior SQL DBA Interview Q&A

By the way though it is about Junior SQL DBA Interview, you may get these questions for mid to senior level DBAs as well.

Junior SQL DBA Interview Q&A:

1.What are the recovery models in SQL Server?

Ans: Simple, Full and Bulk Logged Recovery Model.

2. Explain the Recovery Models and the difference between them?

Ans: The database recovery model setting affects the retention of transaction log entries. The database logs every transaction into the transaction log file irrespective of the recovery model.

But the recovery model determines when SQL Server can truncate (remove/delete)these transactions  from the log file. Whether you can take transaction log backup and the kinds of restore operations.

Simple: 

SQL Server automatically truncates all the committed transactions from the log file when checkpoint occurs.

Each transaction is still written to the transaction log. (Most junior folks have this confusion). Simple recovery logs every transactions and not like because it is says “simple” it does not log all transactions.

It supports the following backup types:

  1. Full backup
  2. Differential backup
  3. Copy-Only backup
  4. File backup
  5. Partial backup

Now since checkpoint removes the log records, it does not supports transaction log backups. And hence point-in-time restores cannot be performed.

Full:

SQL Server truncates the log only after transaction log backup. Most importantly, just like the simple recovery model, it writes all transactions to the transaction log file.

But the main difference is the transactions stays in the log file until a transaction log backup is taken.

It supports all backup types:

  1. Full backup
  2. Differential backup
  3. Transaction log backup
  4. Copy-Only backup
  5. File and/or file-group backup
  6. Partial backup

Since Full recover model writes every transaction to the transaction log, it supports point-in-time restores.

Bulk Logged: 

Bulk-logged recovery model functions similar to the full recovery model with the exception that it minimally logs the transactions log records.

Hence it also truncates the log only after transaction log backup. It also supports all backup types.

The bulk-logged recovery model minimizes transaction log space usage during bulk-logged operations like BULK INSERTSELECT INTO, or CREATE INDEX .

Because it minimally logs the bulk-logged operations, it affects point-in-time recoveries. Read it Again. I didn’t mention that you can’t perform point-in-time recovery.

This is again a misconception. Always remember you can still do a point-in-time restore just like you can in the full recovery model only if no bulk-logged operations are performed at all when the database is using bulk logged recovery model.

3. How recovery model impacts database backups?

Ans: Since recovery model settings determines when the logged transactions are removed from log file it impacts the backup types.

As explained above because log records are removed when a checkpoint occurs, transaction log backups are not supported when using the simple recovery model.

On the other hand in Full recovery model log records are not auto removed when a checkpoint occurs, hence it supports transaction log backups along with Full and Differential.

Bulk logged recovery model behaves exactly like Full recovery model. Hence it supports all backup types.

4. How to check the recovery model of a database?

Ans: There are multiple ways to determine the recovery model of a database. One is to use SQL Server Management Studio tool (SSMS). Another is using T-SQL Code.

Using SSMS:

First right-click on a database, then select the “Properties” item from the drop-down.

SQL DBA Interview

Now from database properties, select the “Options” item from the left context menu. The window will display as shown below:

SQL DBA Interview

Using T-SQL Code:

SELECT name as DbName, recovery_model_desc as RecoveryModel 
FROM sys.databases  
WHERE name = 'Test' ; --Change the database name as applicable
Result:

SQL DBA Interview

 

5.  How to change the recovery model of a database?

Ans: In the same fashion as described above, there are two ways to change the recovery model. One is through SSMS and second is through T-SQL Code.

Using SSMS:

Go to database properties as shown in the answer for question no. 4 and use the dropdown menu to change the recovery model:

SQL DBA Interview

Using T-SQL Code:

USE [master] ; ALTER DATABASE [Test] SET RECOVERY SIMPLE

6. What are the Network Protocols Supported by SQL Server?
Ans:

  1. Shared Memory
  2. Named Pipes
  3. TCP/IP
  4. VIA

Shared Memory:

Clients can use Shared Memory protocol only to connect to  a SQL Server Instance installed on the same server. You can use this protocol for troubleshooting when you suspect other protocols are not working as per expectation.

Failover Clusters does not support this protocol.

Named Pipes:

Basically used by Clients to connect to a SQL Server Instance in the same LAN (Local Area Network). It facilitates Inter-process communication for the machines in the same LAN. That is output of one process is input for the other. Also you can configure the SQL Server instance to listen to one pipe only.

Below Example screen shots shows named pipe for Named and Default Instance :

Named Instance:

SQL DBA Interview

Default Instance:

SQL DBA Interview

 

TCP\IP:

This is the most popular and common protocol. It empowers client to connect to SQL Instance over the Internet (WAN/Any Server within interconnected Network). This protocol includes network traffic routing standards with advanced security protection, connects computers with different hardware and operating systems configurations.

VIA:

Virtual Interface Adapter protocol is used to support VIA devices such as VIA Storage Area Network devices. This protocol supported high performance, clustering, and load-balancing on dedicated network connections. Microsoft already deprecated this feature.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/junior-sql-dba-interview-question-answers/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

How to force manual failover AlwaysOn Group

This article describes how to force manual failover of AlwaysOn Group. A forced failover is a type of manual failover that is strictly for disaster recovery scenarios. Important to realize, in such scenario a planned manual failover is not possible.

Problem: 

For example you have a SQL Server Availability Group (AG) on a Windows Server Failover Cluster (WSFC) with two nodes. Imagine a situation when  one node is completely offline\unavailable or AG Group resource is offline in cluster.

This may cause the AG Group to go into a Resolving state and all the databases in the AG and the Listener will be inaccessible.

Now you must restore the service. In this scenario you have to do force failover and willing to risk losing data.

Solution:

First bring up the WSFC using the forced quorum method. Second Force Failover AG with allow data loss option.

For Example SQLP1 (Primary) is unavailable. WSFC is down and AG is inaccessible.

SQLP2 (Replica) is up and running, and you can connect to SQLP2 from SSMS. The Always On Dashboard lists SQLP2 in the Availability replica, showing AG Group in Resolving state.

ForceQuorum

Recover WSFC using Force Quorum

Important to realize that this is actually forcing a cluster to start without a quorum. Again there are two ways of achieving this. Using powershell and command prompt.

Fix Quorum Using PowerShell:

Start PowerShell using Run As Administrator. You can refer the article Powershell Script to Create Folders where I had shown this step.

Import-Module FailoverClusters
$node = "SQLP2" 
Stop-ClusterNode -Name $node  
Start-ClusterNode -Name $node -FixQuorum
(Get-ClusterNode $node).NodeWeight = 1
Force Quorum Using Command Prompt:

Open command prompt using Run As Administrator. In the first place bring down the cluster service on SQLP2.

C:\>Net stop clussvc

Force Quorum

Now bring up Cluster Service with Force Quorum

C:\>Net start clussvc /forcequorum

Force Quorum

At this moment if you launch WSFC manager, the quorum will show warning as shown in the below screen shot.

Force Quorum

Now the second part of the solution.

Force Always On Failover with Allow data loss:

Now since WSFC is up, the user databases in the AG will appear in the AG Dashboard, but they will be in the Not Synchronizing State.

Force Quorum

We will need to perform a forced AG failover to the SQL Server replica which is still up and running using with allow data loss option. In our example the replica to which we’ll do force failover is SQLP2.

The T-SQL Command to force failover is as follows:

ALTER AVAILABILITY GROUP AGTest FORCE_FAILOVER_ALLOW_DATA_LOSS; 

At this moment the AG should be up and running. Open the AlwaysON Dashboard again and it should now show SQLP2 as Primary and running. This will allow applications to connect to the AG listener and the service is restored.

Force Quorum

When the issue with the server SQLP1 is fixed, they will re-join the WSFC automatically. As long as AG is not removed from other SQL Server instances, they will be able to re-join into the AG as well.

After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.

Resuming of data movement using SSMS:

Force Quorum

T-SQL to resume data movement for the AG:
ALTER DATABASE [AGplaceHolder] SET HADR RESUME;
GO
Summary:

Here we have described a situation when only one node was up in a WSFC without quorum. We performed force quorum to bring up WSFC on a single node (available replica) and brought the SQL AlwaysOn Group back to functional. Once the problem node came up, we resumed data movement to sync the availability databases.

Hope this helps. Please feel free to comment in case of any questions.

You may also interested in the following articles:

How SQL Agent Job Schedule Got Changed Automatically

Interesting facts about SQL Server Startup Parameter -f

Script To List SQL Agent Jobs And Schedules

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/force-failover-alwayson-group/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }