Confusing between MySQL and MSSQL?

This blogs is designed for students OR beginners those are interested to know difference between MySQL and MS SQL. Please don’t make your decision of switching your database platform just after reading this blog rather my suggestion would be to do intensive research and testing in pre – production environment to confirm which technology best suite your business requirement.

MySQL, MSSQL, ORACLE and many more are RDBMS (Relational Database Management System) technology that uses SQL (Structured query language) for insert/update/delete/select rows from table. All above database technology extend their product with their unique feature on top of RDBMS technology. As per some extreme experts none of the above are true RDBMS system.

MySQL is an open-source relational database management system (RDBMS). The MySQL development project has made its source code available under the terms of the GNU General Public License. It was originally developed by Swedish company, MySQL AB, founded by ‘David Axmark’. The first release of MySQL came on 23 May 1995. MySQL AB was acquired by Sun Micro system in 2008 and Sun Micro subsequently acquired by ORACLE in 2010. The latest version of MySQL is 8.0.0-dmr (Milestone Release) was announced 12 September 2016

On the other side, MSSQL (Microsoft SQL)  is developed by Microsoft. The first version of MS SQL was ‘SQL Server 1.0’, a 16-bit server for the OS/2 operating system in 1989. Most of the version so far was only designed to be install and operate from Windows OS, however the latest release ‘SQL 2016’ can be installed on Linux as well. MS SQL comes up with various different edition in which ‘express’ is free with certain limitation and web edition for test environment.

MS SQL is widely used by the companies operating in Bank, Finance & insurance sector as they need to have an assurance of compliance with laws and treaty’s and other legal requirements that open source software cannot provide.

MySQL may not be widely accepted by finance organization but some of the major internet players such as Facebook, google adworks, twitter & wordpress uses MySQL as RDBMS platform.

As I mentioned before, MySQL & MSSQL both are RDBMS system that uses SQL queries as standard way to perform database operation but still there are difference in the syntax both use. Such as one example below:-

---Select all columns of top 10 rows using MySQL query.
SELECT * FROM TestTable WHERE id=12 LIMIT 10

---Select all columns of top 10 rows using MSSQL query.
SELECT TOP 10 * FROM TestTable WHERE id=12

Following two links can be referenced in case of migration from MySQL to MSSQL OR vice versa with key difference in functionality.

MySQL to MSSQL Migration

MSSQL to MySQL Migration

Following certification are listed MySQL Engineer.

Exam Title: MySQL 5.6 Database Administrator
Exam Number: 1Z0-883

Exam Title: MySQL 5.6 Developer
Exam No: 1Z0-882

Following certification are listed MSSQL Engineer.

Exam Title: Querying Microsoft SQL Server 2012/2014
Exam no 70-461

Exam Title: Administering Microsoft SQL Server 2012/2014 Databases

Exam no 70-462

Exam Title: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014
Exam no 70-463

Advertisements

#mssql, #mysql

SQL server database mirroring and practical demonstration.

SQL Server database mirroring is a disaster recovery and high availability technique that involves two or more SQL Server instances on the same or different machines. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Active copy of database is known as ‘Principal’ database & secondary copy is known as ‘Mirrored’ database. SQL Server instance that host principal database known as ‘Primary’ and server that holds mirrored copy known as ‘Secondary’ server.  In special cases, there can be a third SQL Server instance that acts as a Witness.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Please note, Microsoft has depreciated this feature and may not be available in SQL 2016 OR later version of SQL. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always-On Availability Groups instead.


Following steps can be used to setup SQL mirroring for a database reside on two different instances. In my test environment, I am using two SQL instance on different machine (SQL1 & SQL2). Version of SQL is 2014 & operating system is 2012 R2.


Verify SQL services running with domain user credential on both servers running.

1mirroring

On the primary SQL server, execute following script one by one.


----A. Create sample database for mirroring.
USE [master]
CREATE DATABASE MirroringDemo
GO

ALTER DATABASE [MirroringDemo] SET RECOVERY FULL
GO

----B. Create table schema.

USE [MirroringDemo]
CREATE TABLE EmployeeCity
(
ID INT IDENTITY(1,1),
NAME VARCHAR (100),
City VARCHAR(100)
)
GO

----C. Insert 100 records
USE [MirroringDemo]
INSERT INTO EmployeeCity
VALUES
('Pradeep', 'Pune')
GO 100

----D. Take database backup.

BACKUP DATABASE [MirroringDemo]
TO  DISK = N'D:\Backup\MirroringDemo.bak'
GO


On Secondary SQL server, execute following script one by one.

----A. Restore sample database.

USE [master]
RESTORE DATABASE [MirroringDemo]
FROM  DISK = N'D:\Backup\MirroringDemo.bak'
WITH   NORECOVERY
GO

-----B. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

 ----C. Create SQL login for service a/c, if login already exists then leave this step.

USE [master]
CREATE LOGIN [domain\sqlservice] FROM
WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----D. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----E. This is important step, set mirroring partner now. The URL should point to first server. This should always be performed on secondary database first.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL1.Domain.LOCAL:5022'
GO


Go back to primary server, execute following script one by one.

-----A. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

----B. Create SQL login for service a/c, if login already exists then leave this step.
USE [master]
CREATE LOGIN [domain\sqlservice] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----C. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----D. Set mirroring partner now. The URL should point to Second server.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL2.EV.LOCAL:5022'
GO


The database mirroring is ready now. The database will now be visible like below.


2mirroring.jpg


SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes. By default, SQL mirroring works in high safety mode.

High safety without automatic fail-over – Transaction logs are written and committed on the principal and mirror databases synchronously. This might produce delay and slower operation because transaction log must be committed on both databases before considering it secure. This does not provide automatic fail-over and DBA needs to manually change mirrored copy to principal one when primary server is not available.

High safety with automatic fail-over –This require one additional server called as witness server and works same as high safety mode. If the principal database goes down, the automatic fail-over process initiate by witness server, then mirrored database becomes the principal.

High performance – the asynchronous communication, transaction logs are written and committed on the principal server first, and later sent and committed to the mirror server. Automatic fail-over isn’t possible. When primary server is down then DBA needs to manually force mirrored copy to become principal database, this may cause data loss due to asynchronous communication between database. To reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

----Change safety mode
USE [master]
ALTER DATABASE [MirroringDemo] SET SAFETY OFF


Use following syntax’s for pause, resume, remove OR fail-over mirrored database.

----Pause mirroring.
ALTER DATABASE [MirroringDemo] SET PARTNER SUSPEND
GO

----Resume mirroring:
ALTER DATABASE [MirroringDemo] SET PARTNER RESUME
GO

----Failover
ALTER DATABASE [MirroringDemo] SET PARTNER FAILOVER
GO

--Remove mirroring
ALTER DATABASE [MirroringDemo] SET PARTNER OFF
GO

My suggestion of every students to learn how things can be configure using SSMS for understanding the concepts. Configuration using SQL query greatly helps for documentation purpose, reapplying same steps and repetitive task. Here is the slide show of SQL mirroring using SSMS.

This slideshow requires JavaScript.


You can monitor the health of database mirroring by a feature called ‘database mirroring monitor.

13mirroring

14mirroring.jpg


SQL DMV to check mirroring details.

SELECT
               db_name(SM.[database_id])         AS [Database Name]
               ,SM.mirroring_state               AS [Mirror State]
               ,SM.mirroring_state_desc          AS [Mirror State]
               ,SM.mirroring_partner_name        AS [Partner Name]
               ,SM.mirroring_partner_instance    AS [Parnter Instance]
               ,SM.mirroring_role_desc           AS [Mirror Role]
               ,SM.mirroring_safety_level_desc   AS [Safety Level]
               ,SM.mirroring_witness_name        AS [Witness]
               ,SM.mirroring_connection_timeout  AS [Timeout(sec)]
               ,SM.mirroring_redo_queue          AS [REDO QUEUE]
FROM sys.database_mirroring AS SM
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

15mirroring.jpg


All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#database-mirroring, #disaster-recovery, #sql

SQL server Log Shipping overview and practical demonstration.

SQL Server Log shipping allows administrator to automatically send transaction log backups from a primary SQL instance to one or more secondary SQL instances using agent job in background. Following are the benefits of using log shipping feature.

  • Provides a disaster-recovery solution.
  • Supports read-only access on secondary databases.
  • Accidental changes can be avoided as the delay of log copy can be configure based on requirement.

Let’s now see how log shipping can be configured in test environment. Following steps are performed on SQL 2014 with Windows 2012 R2 server as an operation system.

Open SQL Server Manager Studio (SSMS), execute following query to create a sample database, sample table then populate it with 100 initial rows.

USE [master]
CREATE DATABASE [LogShippingDemo]
GO
ALTER DATABASE [LogShippingDemo] SET RECOVERY FULL 
GO
 
--Create a sample table that holds name & city. 
Use [LogShippingDemo]
Create Table EmployeeCity
( 
ID INT IDENTITY(1,1), 
NAME VARCHAR (100),  
City VARCHAR(100)    
)
GO
 
--Insert 100 records
Use [LogShippingDemo]
INSERT INTO EmployeeCity
Values ('Pradeep', 'Pune')
GO 100

USE LogShippingDemo
SELECT COUNT(*) FROM EmployeeCity 

The basic requirement for log shipping is to have SQL agent service running under domain account having permission on shared location that is accessible from SQL server.

Open SQL Server Configuration Manager (SSCM) to confirm agent service is running under domain account.

2log

Open SQL server management studio, select ‘LogShippingDemo’ database then select ‘Transaction log shipping’ option from the database properties.

1log


Define shared location that is accessible from all the SQL servers participating in log shipping. Additionally define agent job properties that will be responsible for taking backup of transaction log from source (or primary) server.

Connect to secondary SQL server and configure secondary database settings. Administration can either take a new set of full backup OR use an existing set of full backup.

4log.jpg


Define the local folder and agent name of secondary SQL server that is responsible for copy transaction log from shared location.

5log.jpg


Select restore job properties and define secondary database state.

6log.jpg

Restoring – This mode is also known as NORECOVERY mode and cannot be used for read operations.

Standby / Read-Only – This mode is also known as STANDBY mode and can be used for read operations. Transactions which are in process on the primary or any uncommitted transactions cannot be read on the secondary database when the database is in a read-only state.

7log.jpg


After completion of above configuration, don’t forget to generate script for above operation for your future reference.

8log.jpg

A successfully completion of log shipping will show following window.

9log.jpg


Let’s now insert, 100 more records in primary database.

USE LogShippingDemo
INSERT INTO EmployeeCity
Values
     ('Pradeep', 'Pune')
GO 100
You may not see updated rows immediately as there could be configuration delay in backup, copy & restore of transaction log. If you wish to see the change then run following job manually.
LSBackup_logShippingDemo Runs on primary server, responsible for copying transaction log on source server and paste it to shared location.
LSCopy_SQL1_LogShippingDemo Runs on secondary server, responsible for copying transaction log from share location to local location of secondary server.
LSRestore_sql1_LogShippingDemo Runs on secondary server, restore transaction log to secondary database.

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#disaster-recovery, #log-shipping, #sql

SQL database Point in time recovery demonstration.

Here in this demonstration, I will show you point in time recovery of database. This approach is really helpful when someone delete records accidentally and we need to restore database before the data deletion.

Before executing backup OR restore activities on SQL server we should understand the basis of backup type and backup strategy.

A Full database backups represent the whole database at the time the backup finished.

Differential database backups contain only changes made to the database since its most recent full database backup. A differential partial backup records only the data extents that have changed in the database since the previous partial backup, known as the base for the differential.

A Backup of transaction logs that includes all log records that were not backed up in a previous log backup.

Full database backup usually taken on weekly basis mostly on weekends during off production hours, differential backups are taken on every day after production hours and transaction logs are taken after every 15 minutes depending on environmental need.

You can copy/paste following script in test SQL environment and run one by one by understanding the operation mentioned in the comment.

-- Create a database called 'BackupRestore’, make sure we have recovery mode set to 'FULL’
USE [master]
CREATE DATABASE [BackupRestore]
GO
ALTER DATABASE [BackupRestore] SET RECOVERY FULL
GO

-- Create table that holds 3 columns.  
Use [BackupRestore]
Create Table EmployeeCity
(
ID INT
IDENTITY(1,1),
NAME VARCHAR
(100),
City VARCHAR(100)
)
GO

-- Insert first three rows. 
Use [BackupRestore]
INSERT INTO [BackupRestore] Values
('Pradeep', 'Pune'),
('Darshan', 'Goa'),
('vikram','Talegon')
GO

--Take Full back up now. 
--In production this is done by backup scripts OR third party application such as VERITAS BackupExec
OR NetBackup. 
 
BACKUP DATABASE [BackupRestore]
TO DISK = N'D:\BackupRestoreTest\BackupRestore.bak'
GO 

-- Insert two more records. 
Use [BackupRestore] 
INSERT INTO EmployeeCity Values
('Ajay', 'Nashik'),
('Yogesh', 'Solapur')
GO

-- Take differential backup.
BACKUP DATABASE [BackupRestore]
TO DISK = N'D:\BackupRestoreTest\BackupRestoreDiff.Bak' WITH  DIFFERENTIAL
GO

-- Insert one more records.
USE [BackupRestore]
INSERT INTO EmployeeCity Values
('Vaishali K', 'Bengaluru')
GO
-- Take transaction log backup. 
BACKUP LOG [BackupRestore] TO  DISK = N'D:\BackupRestoreTest\BackupRestoreTran1.trn'
GO
 
-- Return all rows from table. 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO
 
---Suppose someone delete three rows accidentally. 
DELETE FROM EmployeeCity
WHERE City in ('Bengaluru', 'Solapur','PUNE')
GO
 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO
 
-- Before restoring from backup, a database needs to go in single user mode. 
USE [master]
ALTER DATABASE [BackupRestore] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
 
-- Take Tail log backup of current situation. The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you're going to restore a database to the point of failure, then you need to
take a tail-log backup before you start the restore operation. 

BACKUP LOG
[BackupRestore] TO  DISK = N'D:\BackupRestoreTest\BackupRestoreTran2.trn'
WITH NORECOVERY
GO

-- Restore database using last full backup and keep it with 'NORecovery' that tells SQL Server thatyou are not finished restoring the database and that subsequent restore files will occur. While the database is in this state, the database is not yet
available, so no connections are allowed. 

USE [master]
RESTORE DATABASE [BackupRestore]
FROM DISK = N'D:\BackupRestoreTest\BackupRestore.bak'
WITH  NORECOVERY
GO

-- Restore database using last differential backup. 
RESTORE DATABASE [BackupRestore]
FROM DISK = N'D:\BackupRestoreTest\BackupRestoreDiff.Bak'
WITH NORECOVERY
GO
-- Now all the transaction logs needs to be restored those backup after differential backup those were created before deletion query. We don't need to mentioned 'NOrecovery' as there is no logs to restored and after transaction log restore
we need to make database online. 

RESTORE LOG [BackupRestore] FROM  DISK = N'D:\BackupRestoreTest\BackupRestoreTran1.trn'
-- STOPAT= '08/08/2016 12:12:55′
GO

-- Now if we execute select query then all rows will return those were present before deletion query. 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO

-- Once you complete demonstration then drop 'BackupRestore' database. 
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupRestore'
USE [master]
ALTER DATABASE [BackupRestore] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]
DROP DATABASE [BackupRestore]

 

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.
If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#backup, #restore, #sql-pointintimerecovery

How to create windows Network Load Balance (NLB) for Microsoft Exchange.

What is NLB?

NLB configuration is intended for application with relatively small data sets that does not change frequently such as Web, FTP, VPN to provide high availability, high scalability of client request.

For Exchange services, Load balancing helps distribute incoming client connections over a variety of endpoints to ensure that no one endpoint takes on a disproportional share of the load. Load balancing can also provide fail-over redundancy in case one or more endpoints fails. By using load balancing with Exchange Server 2013, you ensure that your users continue to receive Exchange service in case of a computer failure. Load balancing also enables your deployment to handle more traffic than one server can process while offering a single host name for your clients.

Configuration Steps:-

In my test environment, I have two CAS servers on Windows 2012 R2 using Virtualized environment.

EX2013-CAS2.LAB.LOCAL

EX2013-CAS3.LAB.LOCAL

Install ‘Network Load Balance’ feature from ‘Add roles and Features wizard’.

1-add-remove-feature

2-add-remove-feature.jpg

 

NIC card properties on first CAS (EX2013-CAS2.LAB.LOCAL)

2-nic

 

NIC card properties on second CAS (EX2013-CAS3.LAB.LOCAL)

4-NIC.jpg

 

Change NIC ordering. Production NIC should be at top in both the servers.

5-nic

 

Open Network Load Balancing console and click on ‘New Cluster’.

6-nlb

7-nlb

 

Select NLB network interface and click on ‘Next’.

8-NLB.jpg

 

By default, only one default IP will use. If you wish then add multiple IP.

9-nlb

 

Now click ‘Add’ give IP address of cluster where will be used by client request.

10-NLB.jpg11-nlb

 

Give the full internet name of cluster OR CAS endpoint, in my example it will be ‘mail.lab.local’. select ‘Unicast’ cluster operation mode.

12-nlb

 

Let the default option as it is and click on ‘Finish’.

13-nlb

 

You may see spinning hour glass and the configuration will log at the bottom of the page. Double click on line in case of error during configuration to get detailed error message.

14-NLB.jpg

 

Once the wizard completes successfully, server will be shown in green color.

15-NLB.jpg

 

Now add second CAS server into NLB cluster, the step will most likely be the same.

16-NLB.jpg

 

A successful NLB configuration will show both node (CAS) in green status.

17-nlb

 

Now create a host record in DNS server pointing to IP address of NLB cluster.

dns

You will notice now, the MAC address of NLB NIC on both NIC is same.

20-mac

 

On Exchange shell, run following power shell to force client to use ‘mail.lab.local’.

Get-OutlookAnywhere | Set-OutlookAnywhere -InternalHostname mail.lab.local -InternalClientsRequireSsl $false

You can verify if cluster is reachable using ping command on IP address OR hostname.

IPaddress.jpg

 

Another way to confirm by opening OWA page using cluster name.

18nlb

Additional consideration if you are using virtualized environment.

Go to NLB Manager \ Cluster Properties \ Clusters Parameters Tab and write down the Network address for the NLB cluster.

Shut down the NLB cluster VMs one by one (make sure you don’t shutdown both CAS at a time) then configure the network adapters in ESX/VMware/HyperV that you added to the VMs for the NLB cluster to use a static MAC address that matches the NLB network address: 02-BF-0A-0A-0A-28.

 

Please note:-
All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.
If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#cas, #exchange, #high-availalablity, #nlb