Category Archives: SQL

SysTools SQL Log Analyzer Tool Review – Views of the SQL Expert

There might be cases in where we need to analyze transactions made by the SQL server users. Manually, it was difficult (almost impossible) to carry out this procedure therefore, I decided to spent few dollars on a product like LDF file analyzer. I asked from many of my friends or colleagues for the same and all of them provided their different opinions. A saturation point came in my life when I got frustrated while searching for a solution to read SQL server transaction log. One day I was scrolling down my FB account page where I saw this SysTools SQL Log Analyzer in advertisement section. I clicked on it and read all the reviews posted by the customers. After seeing its rating and reviews, I decided to use this product and thus, download trial version of this. Finally, I found a software for which I was looking from a very long time. Because of this analyzer, now I daily check SQL transaction log without spending my entire day for it. Through this review page, I want to share my working experience with the SQL LDF file reader.

Overview of SysTools SQL Log Analyzer

Product Name SysTools SQL Log Analyzer
Version 3.0
Size of EXE File 6.1MB
Prerequisites MDF file is required for analyzing the log file
Windows OS Windows 10, 8.1, 8, and all below versions
SQL Server Supports log file of SQL server 2016 and all below editions

 SysTools SQL LDF File Reader : Introduction

The software is a great tool to open and analyze SQL server log file records. It enables customers to connect with the online SQL server and extract database & all log file records. This is a Windows-based utility that displays preview of all transaction activities, which are saved in a LDF file. Each record is shown with log activity, time, table name, query, etc. The software shows the total number of records saved in the LDF file, at the time of scanning it. What I personally liked about the product is that it also displays the username who had made changes in transaction. Generally, this feature helps a lot when something goes wrong in the server. The SQL server transactions like Delete, Insert, and update are shown by this product.

S1.png

SysTools SQL Log Analyzer : Its Availability

LDF file viewer is available in 2 editions, which are illustrated below :

  • Free LDF File Reader : The free setup file of this product is available on official site of SysTools. You can download it to check functionality and then, decide whether you want to purchase it or not. This freeware permits users to preview only 50 records per table of a LDF file.
  • Full Version of Tool : In order to view unlimited tables and records, you have to purchase licensed edition of the software. The SQL Log Analyzer full version is further categorized in 3 parts :
    • Personal License : At rate of $299
    • Business License : At the cost of $799
    • Enterprise License : At price of $1499

You can purchase product by making online payment through visa or any other online method. Well, I am using the business license, which is completely fulfilling all my needs to read LDF file.

 Features of SQL LDF File Reader

  1. Extract Records From Live SQL Server : The software is having feature to extract records from Live SQL Server environment. One just needs to mention the server credentials and then, tool will automatically fetch out entire database with records.

S1.png

  1. Supports LDF File of All SQL Versions : SQL Log Analyzer permits users to open, read, and analyze entire log activities made in the server database. It supports extraction of data from SQL server 2016, 2014, 2012, and all earlier versions.

S1.png

  1. Interesting Option to Export Records : The most amazing and unique option of this product is that it is providing 3 option to save the extracted log file records :
    1. Export as SQL Server Database
    2. Export as CSV File
    3. Export as SQL Server compatible script

 

S1.png

  1. Comfortable with All SQL Data Types : SQL LDF file reader supports the set of all advance data type. This includes sql_variant, geometry, Datetime2, geography, and datetimeoffset data types. Apart from this, SQL server installation is needed only to work in online mode with this tool.
  2. Display User Identity in Log Records : A column with name Login Name is shown in preview wizard of the software. You can track the user id of the person who had made transaction in the server. Also, you can learn the time when the transactions were made.

S1.png

  1. Recover Deleted Records from LDF : The SQL LDF file viewer is capable of performing recovery from corresponding corrupted database. It extracts deleted records from the database, if the specified database is in Simple recovery mode.

Pros

  • Instantly performs SQL LDF File recovery process
  • No MS SQL Server is required to view LDF file
  • SQL server credentials are kept safe and secure
  • Filter log records to be exported at targeted path
  • Stores the resultant files at desired PC location
  • Works both with online and offline server mode
  • Also, supports analysis of several NDF file data

Cons

  • If you are using offline mode then, MDF file is mandatory to access LDF data.

Time to Conclude

SysTools SQL Log Analyzer is providing an easiest way to read SQL server transaction log. Depending upon the software performance and working, I would like to rate this utility 9.9 / 10. I am not rating product full because of only one that primary database file is required for working in offline mode

Thanks & Regards

James Smith 
Data Recovery Expert

Error occured while establishing a connection to the SQL server

Recently in one of our application while trying to configure  reporting services configuration we were getting the below error while trying to connect to a SQL database.

IMG2

Checked the remote server connections for the database and it was enabled

SQL4

 

Went into the component services and checked the local DTC connection

 

Test3331

Network DTC access was disabled and hence the issue.

IMG-1.jpg

Enabled them and after MS DTC service restart checked UDL connection for the affected database on that instance.

Final

In addition to the above we can also check the execution account permission on the SQL database server.

This can also happen if the SQL service state is not running.

Make sure SQL Server service status is Running.
Also make sure the TCP/IP communication is enabled on the SQL server configuration manager on the instance where the problematic DB exists

final2

By default SQL Server runs on port 1433, if the default port is changed then these new ports should be added in the firewall exceptions.

You can also check the connectivity to the SQL Server by the below commands

netstat -ano| findstr 1433

You should get a successful  TCP listening establishment on the SQL server IP address and on port 1433 .

Hope this helps

Thanks & Regards 

Sathish Veerapandian 

MVP – Office Servers & Services

Creating SQL instances for messaging related services

We will come across  certain scenarios where we need to integrate our Exchange Servers with other products for additional functionality.

Few examples are installing an automated mail signature software on the Transport Server for customizing and applying the email signatures automatically to all users.

Installing an additional layer of Anti-spam product on the email servers.

Configuring a Meeting room solution for integrating our  room mailboxes,Equipment mailboxes with additional product and integrating with meeting schedule display monitors.

Bringing up an archive solution for all the primary mailboxes and the list goes on.

Most of the products which are coming on the above category will require a centralized database where it needs to store its information related to the  configuration,logs, reports,etc.

In most of the cases these products will be using SQL DB’s for storing these data and values.

So planning in these kind of scenarios is very very important.Since an improper configuration of SQL instance and installation of the related product can interrupt the Exchange functionality very easily.

When we come across these kind of scenarios its always better to interact with the associated product expert .Get recommendations from a SQL expert with regards to the SQL configuration for the product.

I have collected few things which will help an Messaging guy during these scenarios

1) First get in touch with a SQL person and explain him about the requirement of the SQL for the product.Basically what kind of job it writes and stores in the SQL.

2) Reach the associated product expert and get advise on configuring the SQL instance for the product.

If  your environment is or the product that you are going to install is not that complex and does not require SQL enterprise then you can go ahead and install the local SQL instance on the corresponding Exchange Server.

I have collected few points based on my experience which might help during configuring SQL instance if you are going to configure the instance :

Make sure during the installation you select the SQL authentication mode and create a service account for the same. Though the builtin sa account will have the permission but its better to create a dedicated service account for the admin.

SQ

Advantages of selecting SQL server authentication mode

SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used for better security since the users only with Windows authentication cannot connect to the database.

Also make sure that you set the file db and the log file value to some greater figure as below example

SQ1

 

 

Make sure the auto-close option is set to false

Functionality of Auto Close :-

Having this option set to true will annoy us from unwanted SCOM alerts.
When we set this option to True the DB will go offline when it doesn’t receive any active connections and will resume back whenever it gets new connections. So when we have a SCOM agent monitoring this SQL we will get unwanted alerts from them.

Its better to set this parameter to false

Launch Management Studio –> Select the Database  –> Right Click Properties–> Options –> “Auto Close” value to “False”

Autoclsoe

 

After the installation is done check the total and target server memory to see if its eating up any additional memory by executing the below query

Select * from sys.dm_os_performance_counters

where counter_name in

(‘Target Server Memory (KB)’,

‘Total Server Memory (KB)’)

or (object_name=’SQLServer:Buffer Manager’ and counter_name in(‘Buffer cache hit ratio’,

‘Page life expectancy ‘))

 

Also you can check the physical memory ,available committed memory , memory utilization percentage by running the below query

select * from sys.dm_os_process_memory

SQLM

If we are not sure about these values its better after the configuration you can show your SQL installation to an SQL expert and check if all the settings are in right place.

Thanks 

Sathish Veerapandian

MVP – Exchange 

%d bloggers like this: