Ms Sql Server Technology Discussions

[cool_dude]

[quote name='cool_dude' timestamp='1354749400' post='1302902504']
may be idhi vastundi kavachhu try cheyyu

update T1
SET T1.city =T2.City
From Table1 T1
INNER JOIN
(
Select Id,
max(date),
City
From table2 group by ID)as T2
ON T1.ID=T2.ID
[/quote]
[quote name='deals2buy' timestamp='1354749685' post='1302902526']
Msg 147, Level 16, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
[/quote]

Nenu icchina query lo where clause em use chesthalem kadhu bhaiyya..Enduku ala error vastundi..proper ga check chesava query ni..juz asking for conformation...

[cool_dude]

[quote name='ghazni' timestamp='1354749941' post='1302902539']
ani datasetski same vastunda leka oka danike ala avtunda......... try to prse the sp to see if it is running good.......
[/quote]

Only okka dataset ki eey error vastundi..Migitha datasets perfect ga run avtunnayi...

[deals2buy]

[quote name='cool_dude' timestamp='1354749943' post='1302902540']
Nenu icchina query lo where clause em use chesthalem kadhu bhaiyya..Enduku ala error vastundi..proper ga check chesava query ni..juz asking for conformation...
[/quote]
hmmm..intikochesa...repu office ki vellaka try chesta..emaina doubt unte thread leputha malli

[ghazni]

ivala oka query lo gata 5 years ga table variable use chestunaru......... adi almost oka 30 mins tisukuntundi...edo problem vachi naku aa query istey nenu instead of table variables i used CTEs which was good...... ma team lead gadiki adento teliyadanta so i think CTEs better than table variables kada.......... the query ran in 3 mins.......

[deals2buy]

[quote name='ghazni' timestamp='1354750158' post='1302902561']
ivala oka query lo gata 5 years ga table variable use chestunaru......... adi almost oka 30 mins tisukuntundi...edo problem vachi naku aa query istey nenu instead of table variables i used CTEs which was good...... ma team lead gadiki adento teliyadanta so i think CTEs better than table variables kada.......... the query ran in 3 mins.......
[/quote]


naaku CTEs vaade chance intha varaku raaledu..

[cool_dude]

[quote name='deals2buy' timestamp='1354750143' post='1302902559']
hmmm..intikochesa...repu office ki vellaka try chesta..emaina doubt unte thread leputha malli
[/quote]

Ok fine..Gud luck

[cool_dude]

[quote name='ghazni' timestamp='1354750158' post='1302902561']
ivala oka query lo gata 5 years ga table variable use chestunaru......... adi almost oka 30 mins tisukuntundi...edo problem vachi naku aa query istey nenu instead of table variables i used CTEs which was good...... ma team lead gadiki adento teliyadanta so i think CTEs better than table variables kada.......... the query ran in 3 mins.......
[/quote]

Awesome bhaiyya...
Yh CTE are far better than Table Variables...

Nenu eey madhye CTE's ekkuva use chestunna...

[ghazni]

[quote name='deals2buy' timestamp='1354750257' post='1302902567']


naaku CTEs vaade chance intha varaku raaledu..
[/quote]

nijam mama nenu first time oka query ela execute avtundi ani execution plan lo chusa...... table variables kanna CTEs chala better kani na doubt idi nijamganey better aa.. emanna issues untaya........

[ghazni]

[quote name='cool_dude' timestamp='1354750415' post='1302902577']

Awesome bhaiyya...
Yh CTE are far better than Table Variables...

Nenu eey madhye CTE's ekkuva use chestunna...
[/quote]

aitey ok......

[deals2buy]

[quote name='ghazni' timestamp='1354750430' post='1302902579']

nijam mama nenu first time oka query ela execute avtundi ani execution plan lo chusa...... table variables kanna CTEs chala better kani na doubt idi nijamganey better aa.. emanna issues untaya........
[/quote]
cool.. may be CTEs most performance istayemo..and that might be the reason of them being introduced....nuvvu ippudu ekkada untunnav?

[ghazni]

[quote name='deals2buy' timestamp='1354750714' post='1302902598']
cool.. may be CTEs most performance istayemo..and that might be the reason of them being introduced....nuvvu ippudu ekkada untunnav?
[/quote]
Virginia.....

[deals2buy]

[quote name='ghazni' timestamp='1354750765' post='1302902602']
Virginia.....
[/quote]
got new job?

[ghazni]

[quote name='deals2buy' timestamp='1354750788' post='1302902604']
got new job?
[/quote]
already kansas tarvta idi rendo job..... madyalo oka job just 3 weeks antey ........

[deals2buy]

[quote name='ghazni' timestamp='1354750951' post='1302902611']
already kansas tarvta idi rendo job..... madyalo oka job just 3 weeks antey ........
[/quote]
ya SC lo job edo bisket annav kadaa? Ippudu ekkada Richmond aa?

[deals2buy]

[b] Steps to add Log Shipping monitor into an existing SQL Server[/b]


[b] Solution[/b]

The Log Shipping Monitor is used to monitor the overall Log Shipping configuration in your environment. To learn about how to configure Log Shipping please review this tip - [url="http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/"]Step by Step SQL Server Log-Shipping Setup[/url]. If you forget to add the Log Shipping monitor while configuring the Log Shipping in SQL Server 2005, 2008, 2008 R2 or 2012, the conventional wisdom has been you need to remove Log Shipping and reconfigure it with a monitor server.
[b] SQL Server Log Shipping System Tables[/b]

To add the [b]Log Shipping Monitor[/b] on the existing Log Shipping setup we will use these system tables in the MSDB database:[list]
[*][b]log_shipping_monitor_primary - [/b]Stores one monitor record per primary database in each log shipping configuration.
[*][b]log_shipping_monitor_secondary -[/b] Stores one monitor record per secondary database in a log shipping configuration.
[*][b]log_shipping_primary_databases -[/b] Stores one record for the primary database in a log shipping configuration.
[*][b]log_shipping_secondary -[/b] Stores one record per secondary ID
[/list]
[b] SQL Server Log Shipping System Store Procedures[/b]

SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This can be implemented by running the below undocumented log shipping stored procedures:[list]
[*][b]sp_processlogshippingmonitorprimary [/b]
[*][b]sp_processlogshippingmonitorsecondary[/b]
[/list]
[b] Additional Parameters Needed for SQL Server Log Shipping[/b]

You must have the information below handy when you add the Log Shipping Monitoring Server in SQL Server 2005, 2008, 2008 R2 or 2012:[list]
[*]Primary SQL Server Name and Primary Database Name
[*]Secondary SQL Server Name and Secondary Database Name
[*]Monitoring SQL Server name
[/list]
[b] Steps to Add the SQL Server Log Shipping Monitor[/b]

In the below scenario, I used these SQL Servers and databases for adding the log shipping monitor:[list]
[*][b]Primary Server[/b] - D1
[*][b]Primary Database Name[/b] - SQLDBPool
[*][b]Secondary Server[/b] - D1\INST2
[*][b]Secondary Database Name[/b] - SQLDBPool
[*][b]Monitor Server Name[/b] - D1\Monitor
[/list]
The screen shot below indicates that a "Monitor server instance" has not been setup for the SQLDBPool database.

[img]http://www.mssqltips.com/tipimages2/2799_LogShipping.jpg[/img]
[b]Step 1:[/b] Execute the query below on the [b]Primary Server[/b] and make sure you are getting the desired results for your environment. Once the output is confirmed we can use the below Query and specified criteria to prepare the [b]dynamic SQL statement[/b] which will be used to add the [b]Log Shipping Monitor.[/b]

-- Execute the below script on the Primary Server
declare @databaseName varchar(300)
set @databaseName = 'SQLDBPool'
select * FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @databaseName


[img]http://www.mssqltips.com/tipimages2/2799_log_shipping_monitor_primary.jpg[/img]

[b]Step 2:[/b] Execute the below query on the [b]Secondary Server[/b] and make sure you are getting the desired results for your environment. Once the output is confirmed we can use the below Query and specified criteria to prepare the dynamic SQL statement which will be used to add the [b]Log Shipping Monitor[/b].

-- Execute the below script on the Secondary Server
declare @databaseName varchar(300)
declare @primaryServer as varchar(300)
set @primaryServer = 'D1' --Mention the Primary Server Name
set @databaseName = 'SQLDBPool' --mention the primary database name
select * from msdb.dbo.log_shipping_monitor_secondary
where primary_database = @databaseName AND primary_server = @primaryServer


[img]http://www.mssqltips.com/tipimages2/2799_log_shipping_monitor_secondary.jpg[/img]
[b]Step 3:[/b] The next step is to identify the [b]recovery model (i.e. NoRecovery or StandBy) [/b]for the database on the Secondary Server which we will use while configuring the Log Shipping Monitor. Execute the below query on the [b]Secondary Server[/b].

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'SQLDBPool' --mention the Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,restore_mode
from msdb.dbo.log_shipping_secondary_databases
where secondary_database = @databaseName

[b]Step 4:[/b] Assign the value to [b]@Mode[/b] (from the step 3 output) and [b]@monitorServer[/b] (mention monitor instance name) in the below script and execute it on the [b]Primary Server[/b].

-- Run the below script on Primary Server
declare @mode as int
declare @monitorServer varchar(300)
declare @databaseName varchar(300)
set @mode = 1 --mention the output value from step 3
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @databaseName = 'SQLDBPool' --Mention the database name
select
' exec msdb.dbo.sp_processlogshippingmonitorprimary ' + CHAR(13) +
'@mode = ' + CAST(@mode as varchar) + CHAR(13) +
',@primary_id = ' + quotename(primary_id,'''') + CHAR(13) +
',@primary_server = ' + quotename(primary_server,'''') + CHAR(13) +
',@monitor_server = ' + quotename(@monitorserver,'''') + CHAR(13) +
',@monitor_server_security_mode =' + '1' + CHAR(13) +
',@primary_database = ' + quotename(primary_database,'''') + CHAR(13) +
',@backup_threshold = ' + cast (backup_threshold as varchar) + CHAR(13) +
',@threshold_alert = ' + cast(threshold_alert as varchar) + CHAR(13) +
',@threshold_alert_enabled = ' + cast(threshold_alert_enabled as varchar) + + CHAR(13) +
',@history_retention_period = ' + cast (history_retention_period as varchar) + CHAR(13)
FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @databaseName

[b]Step 5:[/b] Copy the output of the script executed in [b]Step 4[/b] and [b]execute[/b] it on the [b]Monitor Server[/b]. Output of the [b]Step 4[/b] is shown below.

--Execute the below script on the Monitor Server
exec msdb.dbo.sp_processlogshippingmonitorprimary
@mode = 1
,@primary_id = '2288E943-27E1-4542-93D9-18312FF5868C'
,@primary_server = 'D1'
,@monitor_server = 'D1\Monitor'
,@monitor_server_security_mode =1
,@primary_database = 'SQLDBPool'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760

[b]Step 6:[/b] Assign the [b]@Mode[/b] (from the step 3 output) and [b]@monitorServer[/b] (mention monitor instance name) in the below script and execute the script on the [b]Secondary Server[/b].

-- Execute below script on secondary server
declare @mode as int
declare @primaryServer as varchar(300)
declare @monitorServer varchar(300)
declare @dbName varchar(300)
set @mode = 1
set @primaryServer = 'D1' -- Mention the Primary Server Name
set @monitorServer = 'D1\Monitor' -- Mention the Monitor Server Name
set @dbName = 'SQLDBPool' -- Mention the Primary Database Name
select
'EXEC msdb.dbo.sp_processlogshippingmonitorsecondary ' + CHAR (13) +
'@mode = ' + cast(@mode as varchar) + CHAR (13) +
',@secondary_server = ' + quotename(secondary_server,'''') + CHAR (13) +
',@secondary_database = ' + quotename(secondary_database,'''') + CHAR (13) +
',@secondary_id = ' + quotename(secondary_id,'''') + CHAR (13) +
',@primary_server = ' + quotename(primary_server,'''') + CHAR (13) +
',@primary_database = ' + quotename(primary_database,'''') + CHAR (13) +
',@restore_threshold = '+ cast (restore_threshold as varchar) + CHAR (13) +
',@threshold_alert = ' + cast(threshold_alert as varchar) + CHAR (13) +
',@threshold_alert_enabled = ' + cast(threshold_alert_enabled as varchar) + CHAR (13) +
',@history_retention_period = ' + cast(threshold_alert_enabled as varchar) + CHAR (13) +
',@monitor_server = ' + quotename(@monitorServer,'''') + CHAR (13) +
',@monitor_server_security_mode = ' + '1' + CHAR (13)
from msdb.dbo.log_shipping_monitor_secondary
where primary_database = @dbName AND primary_server = @primaryServer

[b]Step 7:[/b] Copy the output of the script executed in [b]Step 6[/b] and [b]execute[/b] it on the [b]Monitor Server[/b]. Output of step 6 is shown below.

--Execute the Below script on the Monitor Server
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary
@mode = 1
,@secondary_server = 'D1\INST2'
,@secondary_database = 'SQLDBPool'
,@secondary_id = 'B5F6E3B3-5BEC-42C6-B3CC-7935E58F2BAF'
,@primary_server = 'D1'
,@primary_database = 'SQLDBPool'
,@restore_threshold = 45
,@threshold_alert = 14421
,@threshold_alert_enabled = 1
,@history_retention_period = 1
,@monitor_server = 'D1\Monitor'
,@monitor_server_security_mode = 1

[b]Step 8:[/b] Once you finished with the execution of [b]Step 5[/b] and [b]Step 7[/b] it will create the Log Shipping Alert job on the [b]Log Shipping Monitor Server [/b]as shown below[b]:[/b]
[img]http://www.mssqltips.com/tipimages2/2799_AlertJob.jpg[/img]

[b]Step 9:[/b] Execute the query below on the [b]Primary Server[/b] and it will update the Primary Server Log Shipping table with the Log Shipping Monitoring Server instance name.

-- Script will update monitor Server name into LS system table,
-- Execute it on the Primary Server
USE MSDB
declare @monitorServer varchar(300)
declare @PrimaryDatabaseName varchar(300)
declare @SQL varchar(4000)
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @PrimaryDatabaseName = 'SQLDBPool' --mention the primary database name
select @SQL= 'UPDATE msdb.dbo.log_shipping_primary_databases '
+ 'SET monitor_server = ' + quotename(@monitorServer,'''')
+ ', user_specified_monitor = 1 ' + 'WHERE primary_id = ' + quotename(primary_id,'''')
FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @PrimaryDatabaseName
execute (@SQL)

[b]Step 10:[/b] Execute the query below on the [b]Secondary Server[/b] and it will update the Secondary Server Log Shipping table with the Log Shipping Monitoring Server instance name.

-- Script will update monitor Server name into LS system table
-- Execute it on the Secondary Server
USE MSDB
declare @monitorServer varchar(300)
declare @PrimaryDatabaseName varchar(300)
declare @PrimaryServerName varchar(300)
declare @SQL varchar(4000)
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @PrimaryDatabaseName = 'SQLDBPool' --mention the primary database name
set @PrimaryServerName = 'D1' --mention the primary database name
select @SQL= 'UPDATE msdb.dbo.log_shipping_secondary '
+ 'SET monitor_server = ' + quotename(@monitorServer,'''')
+ ', user_specified_monitor = 1 ' + 'WHERE secondary_id = ' + quotename(secondary_id,'''')
FROM msdb.dbo.log_shipping_monitor_secondary
where primary_database = @PrimaryDatabaseName and primary_server = @PrimaryServerName
execute (@SQL)

[b]Step 11:[/b] Verify the Log Shipping Monitoring is enabled by browsing the following: [b]Database Properties[/b] > [b]Transaction Log Shipping[/b] as shown below:
[img]http://www.mssqltips.com/tipimages2/2799_LogShippingwithMonitor.jpg[/img]
[b]Step 12:[/b] Verify the Transaction Log Shipping Status Report on the Log Shipping Monitoring Server. You can use the Log Shipping Status report by right clicking on the server name in SQL Server Management Studio and navigating to Reports > Standard Reports > Transaction Log Shipping Status. For more information on SQL Server Log Shipping monitoring check out this tip - [url="http://www.mssqltips.com/sqlservertip/2553/different-ways-to-monitor-log-shipping-for-sql-server-databases/"]Different ways to monitor Log Shipping for SQL Server databases[/url].
[b] Next Step[/b]
[list]
[*]Document the SQL Server Log Shipping configuration for all servers in your environment.
[*]Document and customize the above scripts for adding the Log Shipping Monitor on to existing environment.
[*]Create a [url="http://www.mssqltips.com/sql-server-tip-category/27/sql-server-agent/"]SQL Server Agent Job[/url] to run the log shipping status report stored procedure and send an email with the details on a daily basis.
[/list]