Troubleshooting SQL Server Connectivity Issues

When you failed to connect to your SQL Server, the issue could be:

•  Network issue
• SQL Server configuration issue
• Firewall issue
• Client driver issue
• Application configuration issue
• Authentication and logon issue

Please go throught the below link on how to resolve these issues,

https://blogs.msdn.microsoft.com/sql_protocols/2008/04/30/steps-to-troubleshoot-sql-connectivity-issues/

Best Practices

Server Side:

- Check that TCP/IP protocol is enabled in SQL Server Network Configuration section in SQL Server Configuration manager

- Check that SQL Server Service is Running.

- SQL Browser service status is “Running”. (if you have got an instance that is not by default with port 1433)

- Check that Firewall is configured to allow acces to SQL Server by opening the desired port.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access

https://support.microsoft.com/en-us/help/968872/how-to-open-the-firewall-port-for-sql-server-on-windows-server-2008

- Verify whether Remote Connections are accepted by the server or not.

Client Side:

- Verify that the Connection string is correct and pointing to the right server. Be sure on the instance name.

- Check whether the Server is reachable from client machine or not.(use Ping)

- Also try connecting to Server machine out of your application.(use SQLCMD)

SQL Server Resource Governor

Pinal Dave as already written an excellent article on how to configure and use Resource Governor. Please refer that in the below link:

https://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/

Other useful resources:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/resource-governor/
https://www.sqlshack.com/sql-server-2014-resource-governor/
http://www.sqlpassion.at/archive/2014/01/14/how-to-throttle-your-storage-with-resource-governor-in-sql-server-2014/

Enable this feature at your own risk. Go through the below link to know the problems with it.
https://www.brentozar.com/blitz/resource-governor-enabled/ 

To Start SQL Server In Single User Mode

There are certain situations where user wants to start SQL Server Engine in “single user” mode from the start up.

Starting SQL Server in single user mode is very simple process shown below:

Go to SQL Server Configuration Manager and click on SQL Server 2012(Any version that you use) Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter parameter ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server service. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Can we create non clustered index on a primary key column?

Yes we can create non clustered index on a primary key column!

According to the default behaviour of sql server, when we create a primary key column in a table then a clustered index will be automatically created on that particular column.

This default behaviour can be changed. And we can tell sql server not to create any index on primary key column. i.e, we can ask sql server to create primary key column without any index and some other column with clustered index.
So, this is one method in which we can create non clustered index on a primary key column. This is shown in the below way.

CREATE TABLE dbo.Table_1 (
Id int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,SomeOtherUniqueColumn int NOT NULL UNIQUE CLUSTERED ) ON [PRIMARY]

Handling the completely filled Transaction Log problem

Is your Transaction Log full ? And are you not able to execute transactions because of error saying "Transaction Log is full" ?

Problem Explanation:

In SQL Server, we have three recovery models.

1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-Logged Recovery Model.

An SQL Server database has two files by default namely,

• Data file “.mdf”
• Log file “.ldf”

This database should be managed under one of the earlier mentioned recovery models.

NOTE: Taking Transaction Log Backup is the only way to truncate(empty) the log file.

In Simple Recovery model,
SQL Server automatically truncates the log file in regular intervals.
That is the reason why we are not allowed to take a transaction log backup in this mode of recovery (And there is no need to take transaction log backup also in this mode).
So, coming to the main point, log file will never get fulled in this mode of recovery.

In Full Recovery model,
Burden of maintaining and controlling the transaction log is completely left on the DBA.
So, If we dont take regular log backups of the database, then log file will get filled up until the threshold (Refer the NOTE mentioned earlier).

Listed below are some other regular scenarios when transaction log file of SQL Server database grows:

• If there are no scheduled log backups or log backups are not working.
• If there are active transactions in the log file
• If there are long running queries
• In database mirroring if mirror server fails
• In replication if distributor fails
• If there is no disk space to grow the Transaction Log file
• If bulk data was imported without dividing into multiple batches or transactions.

Problem Solution:

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error.

Follow the below mentioned steps to take a transaction log backup.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and select a desired database.

3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4. In the Database list box, verify the database name. You can optionally select a different database from the list.

5. Verify that the recovery model is either FULL or BULK_LOGGED.

6. In the Backup type list box, select Transaction Log.

7. Optionally, you can select Copy Only Backup to create a copy-only backup. Leave this here.

8. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

9. Optionally, in the Description text box, enter a description of the backup set.

10. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

11. To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

12. To view or select the advanced options, click Options in the Select a page pane.

13. Select an Overwrite Media option, by clicking one of the following:

• Back up to the existing media set
  • For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
  • Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
  • Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
  • If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

• Back up to a new media set, and erase all existing backup sets
  • For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

14. In the Reliability section, optionally, check:

• Verify backup when finished.
• Perform checksum before writing to media, and, optionally, Continue on checksum error.

15. In the Transaction log section:

• For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
• To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

16. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

17. SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

After taking transaction log backups, in-active log will be truncated and empty space is created for the upcoming transactions. We can come out of the problem.

In order to prevent the occurrence of this problem in future, log backups should be scheduled at regular intervals according to the server traffic when we have opted for the Full Recovery mode.

Note: The process detailed above will only empty the log file. It will not reduce the log file size.The side reduction of database files is achieved by shrinking them. Shrinking is not a suggested operation. It is always good to maintain adequate log size by taking regular backups. 

Database Snapshots

A Snapshot is a read only copy of the source database as it existed when we created snapshot. 

The following list are some of the properties of database snapshots:

• Snapshot database is completely dependent of the source database.
• If a source database corrupts, the snapshot will also be corrupted.
• We can even restore the entire source database from the snapshot to the time of snapshot creation with below syntax

RESTORE DATABASE db_name
FROM DATABASE_SNAPSHOT = 'snapshotdb_name'

We can run the below query to list all the database snapshots created in a particular database instance.

SELECT * FROM sys.databases 

•  In the result of the above query, the value of 'source_db_id' column will be NULL for all the databases. Because these are original databases.
• So, if the column 'source_db_id' has some value other than null, it indicates that that is a database snapshot. 

We can directly list the database snapshots with the below query.

SELECT * FROM sys.databases 
WHERE source_database_id IS NOT NULL 

 Concept of Database Snapshots

• When a database snapshot is created, an empty 'sparse file' will be created with exactly the number of     pages that the source database consists.
• So, essentially a snapshot starts up empty.
• The database snapshot preserves each page of the source database when the time it (snapshot) was created.
• If few pages are modified in the source database, then those pages ( just before writing the modifications to the disk ) will be copied to the snapshot ( into empty space of those particular pages in the previously created sparse file ).

You can visualize the before mentioned concepts in the below figure:

Not able to understand? Try seeing the below figure:

Got it..!

You can also visualize the above concept in the below animation.

• Snapshot size on disk increases with the updates or modifications made on the source database.
• Source database and the snapshot database will be accessing the same data files.
• That is the reason why, we can run the snapshot on a different host ( source database should be present in this host up and running or available always ) so that the work load can be shared between the source and snapshot database as long as the read-only operations as concern.
• Database snapshots not only give the point in time view of the source database, it also gives point in time transactionally consistent view of the source database.

i.e, if there are any active transactions at the time of snapshot creation, then they must be rolled back.

If a page is copied into the snapshot because of a transaction, then if this transaction rolls back, then the page that is added into the snapshot will not be removed ( One-way operation ). 

This page copying is done synchronously which affects the performance.  

•  Source database and the snapshot database should be located in the same instance. 

Now when a query is submitted on the snapshot database and if the required pages are not present in it, then those pages are brought from source database to serve the query.

No locks taken on reads from the source database when copying the page to snapshots.

Configuring Log Shipping in SQL Server 2008

What is Log Shipping?
Log Shipping is a process that provides an alternative to replication. It starts with a source database (primary database), from which a full backup is taken. This backup is then restored as a new database, which is used as the copy database (secondary database). The source database then provides regular transaction log backups that contain all the changes made to it. These backups are used to restore the changes to the copy database. The process of taking transaction log backups, copying the backup files and restoring the backups is automated via SQL Server Agent. Multiple copy databases can be set up if required.

Log Shipping Features
■ Simple to set up and maintain
■ Reliable and robust
■ Source and copy databases can be completely remote from one another
■ Unlimited number of copy databases can be set up
■ Does not affect the performance of the source database
■ Available to SQL Server 2000*, 2005 and 2008.

Log Shipping Limitations
■ The copy databases can only be full copies of the whole source database; therefore, Log Shipping cannot be used to merge multiple sources into a single central copy database.
■ All copy databases have Read-Only attributes.
■ Automation requires SQL Server Agent; so realistically, Log Shipping is not available with SQL Server Express editions.
■ The backup strategy applied to the source database needs to take Log Shipping into consideration. (*SQL Server 2000 may be problematic in this area.)

Log Shipping Process
This process can be described by the following simple steps:
1. SQL Server Agent job fires on the source server and takes a transaction log backup of the source database, placing the backup file in a shared directory.
2. SQL Server Agent job fires on the copy server and copies the backup file from the shared directory to a
location on its own hard drive.
3. Another SQL Server Agent job fires on the copy server and uses the copied backup file to restore the transactions from the source database to the copy database.

How to do?

Generally log shipping is performed with two different server instances.

But here i am creating two instances in a single server and configuring the log shipping for "credit" database.

You can observe the two instances namely SQLSERVER2008 and INSTANCE1.

I am assuming SQLSERVER2008 as my primary server. So expand it and then right click on the credit database and take a full database backup.

In order to configure a database for log shipping, first we need to enable log shipping for that database. So, First right click on credit database -> select properties -> Transaction log shipping in left menu.

Now enable it to see the below screen.

Click on backup settings button, to see the below screen.

We need to give a network path for a backup share folder even if the backup folder is present in the local machine. Use backup compression option according to your knowledge towards that. And click on "ok" finally when you are done.

Upon clicking "ok",we get the below screen. Click on "Add.." button to add a secondary server.

Then click on "connect" button in the below screen and select the secondary instance in my case ( select any secondary server if  your secondary instance is not on your primary). In order to add more secondary servers, we can add any number of secondary server's with the help of before screen.

And then select the secondary database in the very next drop down list.

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

Since we already taken a full backup of the database in the beginning, we go for the second option. And we need to click on the "Restore Options.." button. Then appear's a new screen in which we need to give the path for the ".mdf" and ".ldf" files of the database in the primary server's instance.

Upon clicking "ok" button, add the path for backup file in the primary server as below.

Mention the folder path in the "copy files" tab for storing the copy of full backup ( which is a result of copy job ) in the secondary server.

In "Restore Transaction Log" tab, select the option the you desired as per your requirement. I am selecting the "standby" option. Click on "ok" upon that.

Now it is upto you to have a monitor server or not. We can perform log shipping without monitor server.
If desired, select the check box in the below screen.

You get the below screen upon clicking that check box followed by "Settings.." button.

Click on "Connect" button to add a monitor server. I am adding it as my secondary server.

Now click on "ok" in the above screen and "ok" in the following screen. You can the see the progress of log shipping configuration.

Click on "Close" upon completion.

Now go back to the secondary server instance and expand it. And then expand the databases folder. You can see the "credit" database in a standby mode (Result of our log shipping configuration).

Now to get the standby database into online, execute the below command in the management studio.

You get an error if you are using the same database which you are trying to get online for executing the above command as below.

Change the using database to master as below.

And the click on "execute" button. You will get the below screen.

Now you can refresh the databases folder for seeing our "credit" database online.

This in my first blog. So, please post some comments upon reading this so that i will improve and correct myself if i need to in my next blog posts.

Thank you everyone for reading!