Start Stop Restart SQL Server from Command Prompt or using Windows Services

SQL SERVER - Start Stop Restart SQL Server from Command Prompt or using Windows Services.

Assalam-o-Alaikum, Dear Fellows here I am going to share with you some of the ways that we use very frequently to start and stop default or named instance of SQL Server.

Click Start>> Run>> type cmd to start command prompt in windows.

To Start default instance of SQL Server
net start mssqlserver

To Start Named instance of SQL Server
net start “SQL Server (mydbsvr)”
net start “SQL Server Agent (mydbsvr)”

– Remember SQL Server Agent can only be start after SQL Server has started. Hence follow the same order that I have mentioned above.
Here “mydbsvr″ is the named instance, this name does not includes my machine name.
Complete name of the instance is SYSNAME\mydbsvr, but here we don’t need complete name we just need instance name.

Figure below is showing how to use net start for start or stop SQL Server default instance;

To Stop default instance of SQL Server
net stop mssqlserver

To Stop Named instance of SQL Server
net stop “SQL Server Agent (mydbsvr)”
net stop “SQL Server (mydbsvr)”

– Remember to Stop SQL Server you first need to stop SQL Server Agent, so first stop SQL Server Agent and then Stop SQL Server, follow the same order as mentioned in the code
The same concept applies here as well, you will not use the complete name of the instance (SYSNAME\mydbsvr), but you will just use instance name without machine name (mydbsvr).

How to Start Stop Restart SQL Server from Windows Services
I will be writing the way to start, stop SQL Server that I use most of the time, whenever I have to copy or transfer my database or logfiles I stop the SQL Server and then copy the database files and then again start the SQl Server Service. For transferring the db files you can also detach and then move to any other location and again attached them but in this way some time detach process takes much time specially when databases are being used by the users. So by stoping the server we can perform this task more easily in comparatively less time. The steps are as described below;

1. Go to Start
2. Go to Run
3. Type Services.msc
4. Search for SQL Server service (There will be the name of SQl Server whether it is default instance or named instance), double click this service.
5. Check for Startup Type, this should not be disabled. This can be Manual or Automatic. If it is disabled, set it to manual, click apply, click Start the Server get started.

I think no need to explain more its pretty straight forward.

How to find out how long a SQL Server backup took

How to find out how long a SQL Server backup took
Written By: Thomas LaRock



Problem
Many shops do full backups of their databases as part of a job within SQL Agent, typically as part of a maintenance plan. This job will often times do all database backups in secession. As such, while you may know how long the job takes, you may not know how long any one particular database takes to have a full backup taken. When deploying changes it is advised to take a full backup of a database prior to deploying the change and a common question faced will be "how long will it take?".

Solution
The information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL.

The T-SQL provided below allows for you to input the name of a database if desired. I have also added a line that will filter the results, limiting your view to only the databases that are currently listed in the master.dbo.sysdatabases table. If you comment out that line of code you will return information on the last time a database was backed up on the instance, regardless if the database is currently listed in master.dbo.sysdatabases.

Returning the details

Here is the T-SQL

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
AND type = 'D' --only interested in the time of last full backup
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

The script will return the following result set:

Column Name


Description
User The name of the user that issued the BACKUP DATABASE command.
Database The name of the database.
Server The name of the server instance.
Backup Started The time at which the backup was started.
Backup Finished The time at which the backup was completed.
Total Time The total amount of time it took to complete the backup for that database.

Images

Here is a screenshot of a sample result set returned by the script.

If you want to get a list of all backups and not just the most recent you can issue the following:

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name



Next Steps

* Take the above code and execute against your instance, making certain to insert the correct database name if you want to filter it to a specific database

Peer-to-Peer Replication in SQL Server 2008

Assalam-o-Alaikum, I was practicing replication that we study in 3rd May’s Class. While practicing I came across peer-to-peer topology I learned more about it and found this article which describers the reasons to use P2P, how to configure P2P and some common issues.

Introduction
Peer-to-Peer (P2P) Replication was introduced in SQL Server 2005 in order to improve availability and performance. In SQL Server 2008 the P2P Replication feature has matured further with the addition of more features. 

Why use P2P Replication
In typical replication scenario, there is a publisher and one or more subscribers as in figure 1.

Figure 1 : Typical Replication Configuration

You can see that in the above scenario, if the subscriber is down, other subscribers and publishers will still work. After the presence of the failed subscriber, it will syncronise with the publisher and will be back into normal operations. However, what if there is a failure in publisher. Well, then that is the failure of the entire system. This means that above configuration will lead to single point of failure.

To address the above issues in typical replication, P2P replication is the option that you can use as shown in figure 2 below:

 

Figure 2: P2P Replication
In P2P replication, each Database acts as a Publisher and a Subscriber. Because of this in this replication topology we won’t call them publisher and subscriber. Instead we call them Nodes as they all are at same level. A transaction originating at one node will be replicated to all other nodes, but not replicated back to originator.

Usage or Benefits of P2P Replication

• All the database queries are spread across multiple nodes. This enables performance to remain consistent as reads increase. Because of this it is recommended to have the same configurations for all the databases so that users will not be able to identify which node they are connected to.
• If one of the nodes in the system fails, an application layer can redirect the writes for that node to another node. This maintains availability.
• If a node requires maintenance or the whole system requires an upgrade, each node can be taken offline and added back to the system without affecting the availability of the application.

Figure 3, shows the implementation of P2P Replication in three different cities London, Los Angeles and Taipei. 

Figure 3: P2P Implementation   

Image Source: MSDN

See more details of How to implement Peer-to-Peer replication topology in SQL 2008.

Difference between MySQL & SQL Server

Introduction

One of the more common themes present on the various MySQL forums and mailing lists is that of data migration. Typically requests are made by users of Microsoft® Access and Microsoft SQL Server who are looking to migrate their data (and client applications) to a MySQL database. Developers often ask for tools that can be used to convert an Access database to MySQL (or convert an MSSQL database to MySQL), without realizing that there is more to migrating an application to MySQL than simply converting data.

In this article I will cover the basics of migrating an application from an Access or SQL Server database to MySQL. We'll start with various reasons why you should (or should not) migrate your existing Access or SQL Server database to MySQL, then cover the planning stages of an application migration. Next we will look at the tools and methods for migrating your actual data from Access/MSSQL to MySQL, followed by some general guidelines for modifying your client application from a Microsoft database to MySQL. Finally, we'll look at some considerations to make when deploying your new MySQL database and application.

Why people prefer to migrate to MySQL

Chances are good that if you are reading this article you already have an interest in migrating your application from Access or SQL Server to MySQL, or at least add support for MySQL to your existing Windows® application. The reasons for migrating an application vary, but let's look at a few of them.

MySQL is Cross-Platform

One great advantage of using MySQL is its cross-platform capabilities. You can develop your database on a Windows laptop and deploy on Windows Server 2003, a Linux server, an IBM mainframe, or an Apple XServe, just to name a few potential platforms. This gives you a lot of versatility when choosing server hardware. You can even set up replication using a master on a Windows platform with Linux slaves. It's incredibly easy to move between platforms: on most platforms you can simply copy the data and configuration files between servers and you are ready to go!

MySQL is Fast

An independent study by Ziff Davis found MySQL to be one of the top performers in a group that included DB2, Oracle, ASE, and SQL Server 2000. MySQL is used by a variety of corporations that demand performance and stability including Yahoo!, Slashdot, Cisco, and Sabre. MySQL can help achieve the highest performance possible with your available hardware, helping to cut costs by increasing time between server upgrades.

MySQL is Free

MySQL is Open Source software. As such you are free to examine the source code and make any changes you wish. As per its GPL license, you are free to redistribute those changes as long as your software is also Open Source. If you do not wish to make your software Open Source, you are free to do so as long as you do not distribute your application externally. If you adhere to the requirements of the GPL, MySQL is free for you to use at no cost. If you wish to distribute your closed-source application externally, you will find that the cost of a MySQL commercial license is extremely low (MySQL licenses start at only $249 US). MySQL AB also offers well priced commercial support that is significantly less expensive than some of its counterparts.

Difference between MySQL And SQL SERVER………….

MySQL does not support Triggers, SQL Server does.

MySQL does not support User Defined Functions, SQL Server does.

MySQL does not have Cursor Support, SQL Server does.

MySQL does not support Stored Procedures, SQL Server does.

MySQL does not have FULL JOIN capabilities, SQL Server does.

MySQL has limited Import/Export capabilities, SQL Server has full support for Import/Export.

SQL Tips & Tricks - Part - I

Current Database - SQL Server

What is the Current Database?
Select DB_NAME() as Curr_DB

--How to Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

First day of the week - @@datefirst

The first day of the week will be 7 - Sunday (default for US English)
Select @@datefirst as FirstDay

You can set it by :
Set datefirst 1;

No of Records using Select Query

-- No of Total Records using Select Query
Select count(*) from Purchasing.PurchaseOrderDetail

-- No of Records for each group using Select Query
Select PurchaseOrderID, count(*) NoOfRec from Purchasing.PurchaseOrderDetail Group By PurchaseOrderID

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null

Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo

How to Get Part of the Date - DatePart in SQL

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date

Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

SQL Server examples of DDL, DML, DCL & TCL Commands

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT - Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE - Updates existing data into a table
DELETE - Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE - Creates objects in the database
ALTER - Alters objects of the database
DROP - Deletes objects of the database
TRUNCATE - Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT - Gives user’s access privileges to database
REVOKE - Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT - Saves work done in transactions
ROLLBACK - Restores database to original state since the last COMMIT command in transactions

DQL - Data Qeury Language

SELECT is also categorized as DQL.