SEND DBMAIL USING QUARYS AND WIZARD LINK and ONE PRCEDURE EXAMPLE

BY USING QUARY TO SEND DBMAIL

---database mail
----right click on servre select facets the facet select 'surface area configuration'
    inthis options we enable true%
---- we check configure advanced options
    sp_configure

sp_configure 'show advanced options',1
reconfigure

----in advanced options we take sql mail xps and enable it

sp_configure 'SQL Mail XPs',1
reconfigure

-----dbmails done with only msdb system database
-----add profile name (any name),we select sysmail_add_profile_sp the click alt+f1 see
     the optinal parameter names and give the name

sysmail_add_profile_sp @profile_name='babji',@description='test2'

---add princepalprofile name

sysmail_add_principalprofile_sp @profile_name='babji',
@principal_name='public',
@is_default=1

---add account name ,where account name and profile name should be same,
    display name(any),replay_address(same mail_id or other valid mail id),
    mail_server(here deffinetly we will give proper mail server ,below i gave
    microsoftserver and anybody try with gamil server etc )

sysmail_add_account_sp @account_name='babji',@email_address='babji.reddy1@gmail.com'
,@display_name='junnu',@replyto_address='babji.reddy1@gmail.com',
@mailserver_name='tk3ptmsg01.parttest.extranettest.microsoft.com'

----add profile name to account name with sequence no

sysmail_add_profileaccount_sp @profile_name='babji',@account_name='babji',@sequence_number=1

----then we will send mail here body (mail body information),and subject

sp_send_dbmail @profile_name='babji',@recipients='babji.reddy1@gmail.com',
@body='vvasd',@subject='testsqlmail'

------then we check maill send or not by using msdb system detabase views

select * from dbo.sysmail_sentitems
select * from dbo.sysmail_faileditems
select * from dbo.sysmail_allitems
 select * from dbo.sysmail_event_log

BY USING WIZARD TO SEND MAIL LINK:

http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml           




SEND DBMAIL EXAMPLE:

   create table tbl_insurance(ins_id int identity ,name varchar(30), 
   vehicletype varchar(20), mail_id varchar(50),phonenu bigint,exp_date date)

insert into tbl_insurance values('chintu','lenova','chinnu@gmail.com',9652067541,'06-30-2015'),
                                ('minnu','yamaha','meganareddy.aa@gmail.com',8096955421,'09-24-2015'),
                                 ('bhavani','car','bhavanireddy.mm@gmail.com',9703435671,'06-10-2014'),
                                 ('babji','yamaha','babji.reddy1@gmail.com',7396042168,'06-20-2014')
   select * from tbl_insurance                               

  select * from tbl_insurance where exp_date between '06-01-2014' and '06-30-2014'


----- by using dbsend_mail send mail to this month exriry date candidates or from now any exripy       candidates r there then send mail
       go
        create proc usp_expdate_sendmail
         as
         begin
         declare @exp_date varchar(50)
 declare @mail_id varchar(50)
  declare @body varchar(max)
 declare @veh_type varchar(50)    
  declare @name varchar(20)
 declare @str varchar(max)
 declare c1 cursor forselect mail_id,exp_date from  tbl_insurance where exp_date                                                between getdate() and dateadd(day,30,getdate());

        open c1

fetch next from c1 into @exp_date,@mail_id

      while(@@FETCH_STATUS=0)

                 BEGIN
    set @body='ur vehicle insurence is expires on :' +@exp_date;
    exec msdb.dbo.sp_send_dbmail@profile_name='bhavani',@recipients=@mail,@body=@body
    fetch next from c1 into @exp_date,@mail_id
        end
   
close c1
deallocate c1
 end

SUPER AGGREGATE FUNCTIONS,GROUPING AND COMPUTE BY

• super aggregates means aggregates of aggregates
• two types super aggregates 

                   1.cube 
                   2.rollup

syntax:      
                          SELECT column_list
                           FROM table_list
                           WHERE search_criteria
                           [GROUP BY [ALL] non_aggregate_expression(s)
                           [WITH {ROLLUP | CUBE} ]]

• single column group by rollup and cube get same output rollup

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO WITH CUBE

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO WITH ROLLUP

SELECT ISNULL(DEPTNO,0),SUM(SAL) FROM EMP GROUP BY DEPTNO WITH CUBE

• mutiple columns group by rollup and cube get different o/p.

• multiple columns group by with rollup and cube

     SELECT DEPTNO,JOB,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO,JOB                WITH ROLLUP

     SELECT DEPTNO,JOB,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO,JOB                WITH CUBE

grouping function:

• By using grouping function to replace null vaues in rollup and cube

                  select * from emp

rollup: 

 SELECT 

       case GROUPING(DEPTNO)
       WHEN 1 THEN 'DEPT_TOTAL'
       ELSE CAST(DEPTNO AS VARCHAR(10))
       END AS DEPTNO,
       case GROUPING(JOB)
       WHEN 1 THEN 'JOB_TOTAL'
       ELSE JOB
       END AS JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB WITH ROLLUP

cube:

 SELECT 

       case GROUPING(DEPTNO)
       WHEN 1 THEN 'DEPT_TOTAL'
       ELSE CAST(DEPTNO AS VARCHAR(10))
       END AS DEPTNO,
       case GROUPING(JOB)
       WHEN 1 THEN 'JOB_TOTAL'
       ELSE JOB
       END AS JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB WITH CUBE

compute by:

•     Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.
• This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ROLLUP instead.

SELECT * FROM EMP1

SELECT * FROM EMP ORDER BY DEPTNO,JOB COMPUTE COUNT(EMPNO),COUNT(SAL),MAX(SAL),MIN(SAL) BY DEPTNO


SELECT * FROM EMP ORDER BY JOB,DEPTNO

SELECT * FROM EMP COMPUTE SUM(SAL)

SELECT * FROM EMP ORDER BY DEPTNO COMPUTE SUM(SAL) BY DEPTNO

SELECT * FROM EMP ORDER BY JOB COMPUTE COUNT(SAL),MAX(SAL),MIN(SAL) BY JOB

SELECT * FROM EMP ORDER BY JOB,DEPTNO COMPUTE COUNT(SAL),MAX(SAL),MIN(SAL) BY JOB,DEPTNO

FIND HIGHEST COLUMN COUNT IN A TABLE WITH IN A DATABASE

 select * from INFORMATION_SCHEMA.columns


 SELECT TOP 1 TABLE_NAME ,COUNT(COLUMN_NAME) AS CNT
 FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY 2 DESC

CHANGE RECOVERY MODEL BY USING QUARY

--------BY USING QUARY TO CHANGE RECOVERY

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'manju1'

USE [master]
GO
ALTER  DATABASE  [manju1]  SET RECOVERY FULL WITH  NO_WAIT
GO

ALTER DATABASE [manju1] SET RECOVERY SIMPLE WITH  NO_WAIT

select * from sys.databases 

STEP BY STEP SQL SERVER LOG SHIPPING

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

Permissions

To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements

1. SQL Server 2005 or later
2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
3. The servers involved in log shipping should have the same case sensitivity settings.
4. The database must use the full recovery or bulk-logged recovery model
5. A shared folder for copying T-Log backup files
6. SQL Server Agent Service must be configured properly

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarly used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure Log-Shipping:

1. Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases 

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'jugal'

USE [master]

GO

ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT

GO

2. On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary database in a log shipping configuration" check box.

3. The next step is to configure and schedule a transaction log backup. Click on Backup Settings... to do this.

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

4. In this step we will configure the secondary instance and database. Click on the Add... button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

When you click the Add... button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect... button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database tab

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.

Copy Files Tab

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

Restore Transaction Log Tab

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

5. In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

Click on Settings... button which will take you to the "Log Shipping Monitor Settings" screen. Click on Connect ...button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

6. Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

Next Steps

• As Log Shipping does not support automatic failover, plan for some down time and a manual failover
• Once you failover, check for Orphan Users and fix as needed
• For VLDBs it is recommended that you manually restore the database instead of using the wizard to create the full backup.

CONFIGURE SQL SERVER DATABASE MIRRORING USING SSMS

I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.

1st step: Issue a full backup of the database.

DATABASE BACKUP TestMirror TO DISK = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';

2nd step: Issue a transaction log backup of the database.

BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn'; 

Below are the two files in the file system:

3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
NORECOVERY, NOUNLOAD, STATS = 10;

4th step: Restore log backup also with the NORECOVERY option.

RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' 
WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".

Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:

This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.

Select "No", then click "Next >" to continue the process.

The next screen will give you options to configure the Principal Server Instance:

Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.

Click the "Next >" button to continue.

The next screen will give you options to configure the Mirror Server Instance:

To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:

Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.

Click "Next >" and you'll see the Service Accounts screen.

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).

If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.

Since my service accounts are using the same domain account, I'll leave this blank.

Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.

If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...

Next screen that pops up should be the Start/Do Not Start Mirroring screen:

We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:

Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.

For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.

Next, click "Start Mirroring" as shown below.

If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.

 

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:

Both servers should be listening on the same port. To verify this, run the following command:

SELECT type_desc, port 
FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:

Database mirroring should be started on both servers. To verify this, run the following command:

SELECT state_desc
FROM sys.database_mirroring_endpoints;

The state_desc column on both the Principal and Mirror server should be started:

To start an Endpoint, run the following:

ALTER ENDPOINT <Endpoint Name>
STATE = STARTED 
AS TCP (LISTENER_PORT = <port number>)
FOR database_mirroring (ROLE = ALL);

ROLES should be the same on both the Principal and Mirror Server, to verify this run:

SELECT role 
FROM sys.database_mirroring_endpoints;

To verify the login from the other server has CONNECT permissions run the following:

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

You can see here from the State and Permissions column that the user has been Granted Connect permissions.

Next Steps

• To learn more about the three different operating modes involved in database mirroring check out this previous tip
• Before implementing database mirroring make sure this is the high availability option you need for you company. Log shipping, replication, and clustering are also high availability options that may bring more benefit than mirroring depending on the needs.
• Check out all of the Database Mirroring tips.

LOG SHIPPING VS. MIRRORING VS. REPLICATION

Log Shipping::

It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled. 

Mirroring::

Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

Replication::

It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components

Log Shipping::Primary server, secondary server and monitor server (Optional).
Mirroring::Principal server, mirror server, and witness server (Optional).
Replication::Publisher, Subscribers, Distributor (Optional).

Data Transfer

Log Shipping::T-Logs are backed up and transferred to secondary server.
Mirroring::Individual T-Log records are transferred using TCP endpoints.
Replication::Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.

Server Limitation

Log Shipping::It can be configured as One to Many. i.e one primary server and many secondary servers. Or
Secondary server can contain multiple Primary databases that are log shipped from multiple servers.
Mirroring::It is one to one. i.e. One principal server to one mirror server.
Replication::

• Central publisher/distributor, multiple subscribers.
• Central Distributor, multiple publishers, multiple subscribers.
• Central Distributer, multiple publishers, single subscriber.
• Mixed Topology.

Types Of Failover

Log Shipping::Manual.
Mirroring::Automatic or manual.
Replication::Manual.

DB Access

Log Shipping::You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
Mirroring::Mirrored DB can only be accessed using snapshot DB.
Replication::The Subscriber Database is open to reads and writes.

Recovery Model

Log Shipping::Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model.
Mirroring::Mirroring supports only Full Recovery model.
Replication::It supports Full Recovery model.

Restoring State

Log Shipping::The restore can be completed using either the NORECOVERY or STANDBY option.
Mirroring::The restore can be completed using with NORECOVERY.
Replication::The restore can be completed using With RECOVERY.

Backup/Restore

Log Shipping::This can be done manually or
through Log Shipping options.
Mirroring::User make backup & Restore manually.
Replication::User create an empty database with the same name.

Monitor/
Distributer/ Witness

Log Shipping::The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. . If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.
Mirroring::Principal server can’t act as both principal server and witness server.
Replication::Publisher can be also distributer.

Types Of Servers

Log Shipping::All servers should be SQL Server.
Mirroring::All servers should be SQL Server.
Replication::Publisher can be ORACLE Server.

SQL Server Agent Dependency/Jobs

Log Shipping::Yes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
Mirroring::Independent on SQL Server agent.
Replication::Yes. Snapshot agent, log reader agent & Distribution agent (transactional replication)
Merge agent (merge replication).

Requirements

Log Shipping::

• The servers involved in log shipping should have the same logical design and collation setting.
• The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.
• The SQL server agent should be configured to start up automatically.
• You must have sysadmin privileges on each computer running SQL server to configure log shipping.

Mirroring::

• Verify that there are no differences in system collation settings between the principal and mirror servers.
• Verify that the local windows groups and SQL Server logins definitions are the same on both servers.
• Verify that external software components are installed on both the principal and the mirror servers.
• Verify that the SQL Server software version is the same on both servers.
• Verify that global assemblies are deployed on both the principal and mirror server.
• Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.

Replication::

• Verify that there are no differences in system collation settings between the servers.
• Verify that the local windows groups and SQL Server Login definitions are the same on both servers.
• Verify that external software components are installed on both servers.
• Verify that CLR assemblies deployed on the publisher are also deployed on the subscriber.
• Verify that SQL agent jobs and alerts are present on the subscriber server, if these are required.
• Verify that for the certificates and keys used to access external resources, authentication and encryption match on the publisher and subscriber server.

Using With Other Features Or Components

Log Shipping::Log shipping can be used with Database mirroring, Replication.
Mirroring::Database mirroring can be used with
Log shipping, Database snapshots , Replication.
Replication::Replication can be used with log shipping, database mirroring.

DDL Operations

Log Shipping::DDL changes are applied automatically.
Mirroring::DDL changes are applied automatically.
Replication::only DML changes to the tables you have published will be replicated.

Database Limit

Log Shipping::No limit.
Mirroring::generally good to have 10 DB’s for one server.
Replication::No limit.

latency

Log Shipping::There will be data transfer latency. >1min.
Mirroring::There will not be data transfer latency.
Replication::Potentially as low as a few seconds.

Committed /
Uncommitted
Transactions

Log Shipping::Both committed and uncommitted transactions are transferred to the secondary database.
Mirroring::Only committed transactions are transferred to the mirror database.
Replication::Only committed transactions are transferred to the subscriber database.

Primary key

Log Shipping::Not required.
Mirroring::Not required.
Replication::All replicated table should have Primary Key.

New Created Database&
Stored Procedure

Log Shipping::Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.
Replication::Creates new SPs ( 3 Sps of one table).
Distribution Database.
Rowguid column will be created.

Individual Articles

Log Shipping::No. Whole database must be selected.
Mirroring::No. Whole database must be selected.
Replication::Yes. Including tables, views, stored procedures, and other objects. Also filter can be used to restrict the columns and rows of the data sent to subscribers.

FILESTREAM

Log Shipping::Log shipping supports FILESTREAM.
Mirroring::Mirroring does not support FILESTREAM.
Replication::Replication supports FILESTREAM.

DB Name

Log Shipping::The secondary database can be either the same name as primary database or it may be another name.
Mirroring::It must be the same name.
Replication::It must be the same name.

DB Availability

Log Shipping::In case of standby mode: read only database.
In case of restoring with no recovery: Restoring state.
Mirroring::In Recovery state, no user can make any operation.
You can take snapshot.
Replication::Snapshot (read-only).
Other types (Database are available).

Warm/ Hot Standby Solution

Log Shipping::It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring::When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Replication::It provides a warm standby solution that has multiple copies of a database and require a manual failover.

System Data Transferred

Log Shipping::Mostly.
Mirroring::Yes.
Replication::No.

System Databases

Mirroring::You cannot mirror the Master, msdb, tempdb, or model databases.

Mode Or Types

Log Shipping::

• Standby mode (read-only)-you can disconnect users when restoring backups .
• No recovery mode (restoring state)-user cannot access the secondary database.

Mirroring::

• high-safety mode supports synchronous operation.
• high-performance mode, runs asynchronously.
• High-safety mode with automatic failover.

Replication::

• Snapshot replication.
• Transactional replication.
• Transactional publication with updatable subscriptions.
• Merge publication.
• Pull/Push subscription.