Click here to download presentation on this topic.
Most of the times both these terms are used interchangeably however we should have a clear distinction of these in mind. Particularly while dealing with the disaster recovery (DR) and high availability (HA) scenarios.
One word difference
Major difference lies in one word i.e. data. HA does not involve data recovery aspects while DR do as name indicates.
High availability (HA)
- It is the system’s ability to remain accessible in the event of a system component failure
- It involves multiple levels of fault tolerance and/or load balancing capabilities into a system
- It does not include the redundancy or data recovery abilities
- Service downtime does not exist or is in seconds
Disaster Recovery (DR)
- It is the process by which a system is restored to a previous acceptable state, after a natural or man-made disaster
- DR involves recovery of data and services within some down time
- Downtime depends upon the option used along with the HA involved
- For DR, measurable characteristics, such as Recovery Time Objective (RTO) and Recovery Point Objective (RPO) drive our DR plan
In previous post i have discussed the ports configuration for SQL Server database engine. For any SQL Server instance what are the options to identify the active ports? For this purpose we have three major methods to identify the SQL Server port through GUI. We may use undocumented extended stored procedure master..xp_regread system to read the registry value for this purpose.However in this post i would cover only the major GUI methods to discover the active TCP/IP port for any SQL Server instance.
- Through SQL Server Logs
- Through SQL Server Configuration Manager
- Through Windows Application Event Viewer
Through SQL Server Logs
We may use xp_readerrorlog SP in SSMS or SSMS GUI to determine the current port for SQL Server. Execute the SP in SSMS query pane and get the results
USE master GO xp_readerrorlog 0, 1, N'listening on', 'any', NULL, NULL, N'asc' GO
Same may be determined through SSMS GUI
Through SQL Server Configuration Manager
Go to SQL server configuration manager and open TCP/IP properties in network configurations of specific instance. It would show the active TCP/IP port for SQL Server. Port may be noted or even changed to a specific static one here.
Through Windows Application Event Viewer
Another major option available for port discovery is through Windows event viewer. Find the event viewer in Windows Administrative Tools and go to application logs folder. There you may get the port information for your specific SQL Server instance.
This is not the end, there would be some other ways to discover the same information. Also read the post and comments section on MSSQLTips for same issue.
I came across an interesting question. To get those values of table1 that are not there in table2. This task can be completed very easily by using the EXCEPT operation. However i was required to get the result by appending just a single condition in provided T-SQL code. The trick was about using joins concept to get the required result. Create and populate two tables through following code.
-- Create first table create table Table1 (id smallint, Item varchar(25)) GO -- Create second table create table Table2 (id smallint, Item varchar(25)) GO -- Populate table1 insert into table1 select 1,'Item1' union all select 2, 'Item2' union all select 3, 'Item3' union all select 4, 'Item4' GO -- Populate table2 insert into table2 values (3,'Item3') GO
The required result is as shown in the snap
And just add a single condition either in join clause or in where clause of following query to get the result
-- Complete the script to get required result select t1.* from table1 t1 left outer join table2 t2 on t1.id = t2.id and t1.item = t2.item -- Add a condition here to get the required result
And below is the single line condition that is required to get the required result
where t2.id is null
Complete query would be as following
-- Complete script to get required result select t1.* from table1 t1 left outer join table2 t2 on t1.id = t2.id and t1.item = t2.item where t2.id is null
WITH clause when used in backup statement has various associated options. I have discussed effect of couple of these associated options
In most of the cases both of above options do not make major difference when used while creating backups on disk rather than tape drive. However i got error when using these options for mirror backups. For mirror backups INIT was not producing same results as that of FORMAT. Consider following scenarios
Try to create full backup of AdventureWorks along with a mirror backup. In following script INIT option is provided
BACKUP DATABASE AdventureWorks TO DISK = 'C:AdventureWorks.bak' MIRROR TO DISK = 'C:AdventureWorks1.bak' WITH INIT GO
Previously backup device is not present but mirror backup script would produce following error even with or without INIT option.
Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
By providing WITH FORMAT argument as suggested in error message, backup along with mirror backup would be created successfully
Once mirrored media set is created you may append later backups to same mirrored media set without using WITH FORMAT. In case of adding new mirror to existing media WITH FORMAT would be required again.