Search

Advertisements

Showing posts with label Administration. Show all posts
Showing posts with label Administration. Show all posts

SQL Server - Example for invoking sql file through SQLCMD

SQLCMD utility lets us execute Transact-SQL statements, Stored procedures, and other script files at the command prompt,
in SSMS Query Editor in SQLCMD mode,
in a Windows script file or
in an operating system (Cmd.exe) job step of a SQL Server Agent job.

In this post, let us see an example for invoking sql file through SQLCMD

SQL Server - Transactional Replication - Configuring subscriber

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring subscriber at server 3

In SSMS - > Under Object Explorer - > Replication -> Local Subscriptions -> New Subscriptions...

SQL Server - Transactional Replication - Configuring Publisher

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring publisher at server 2

In SSMS - > Under Object Explorer - > Replication - > Local Publications -> New Publication...

SQL Server - Transactional Replication - Configuring distributor

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring distributor at server 1

In SSMS - > Under Object Explorer - > Replication - > right-click Configure Distribution...

SQL Server - Transactional Replication

As mentioned in MSDN BOL :

Transactional replication typically starts with a snapshot of the publication database objects and data.

As soon as the initial snapshot is taken, subsequent data changes made at the Publisher are usually delivered to the Subscriber as they occur (in near real time).

How Transactional Replication Works

SQL Server - SHOWPLAN permission denied in database - Error - Msg 262, Level 14, State 4, Line 1

When a User with minimum privilege try to view the execution plan for a query, below error will throwed:

Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database 'AdventureWorks2012'.



To resolve the error:

SQL Server - Dynamic SQL - SQL Injection - EXEC [ QUOTENAME() , REPLACE() , EXECUTE AS ] - Sp_executesql - [ RECOMPILE ]

Recently I had mess up with dynamic SQL , So whats next !!! , I started to explore on this topic and post it here , when I googled to gather some information , but what I found was , this topic has been already drilled to the core , carved and with the sculpture mounted on the walls of SQL Server by some of the great SQL Server guru's , much before an year I started to work with SQL Server .

For my regular readers , I will definitely share the good links with the abstract from the same .

To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution”  (DSE)

As mentioned in the BOL :

SQL Server - Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event  -  " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .

SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions


Powershell Command to get status of SQL Server services

Powershell Command to get status of SQL Server services in a machine.

#to get stopped services
Get-Service -name *sql* | Where-object{$_.Status -eq "Stopped"} | Format-Table -AutoSize

#to get Running services
Get-Service -name *sql* | Where-object{$_.Status -eq "Running"} | Format-Table -AutoSize



SQL Server 2012 - File Table

To enable Filestream while installing SQL Server 2012



To enable after installing & creating database :
Goto - > SQL Server Configuration Manager - > SQL Server Services -> double-click on SQL Server



To set Filestream directory name & access level ,while creating new database




SQL Server - Database Mail

This post is about sending mail through SQL Server [Database Mail  feature in SSMS]







select the option to create profile & account for sending mail.




SQL Server - Database Read - Only

After attaching sample database - AdventureWorks2012,I noticed the database in Read-Only state as shown in below image :



SQL Server 2008 - Data Collection



What is Data Collection in SSMS 2008?


Data Collection option in SSMS 2008 is used to gather data about Disk Usage,Query Statistics,Server Activity and generate report about 

1.)Disk Usage Summary:
disk space used by databases and
data file & log file growth rate of a database in a SQL Server 2008 instance.

2.)Query Statistics History:
execution count of a query,
total duration for a query execution,
I/O cost & CPU utilization of a query in a SQL Server 2008 instance.

3.)Server Activity History:
resource consumption details like CPU,memory,disk i/o & network usuage,
SQL Server waits,
SQL Server activities like (user connections,logins,logouts,transactions,batch requests and SQL compilations & recompilations) for a SQL Server 2008 instance and also for host OS where data collection option is configured.

Where  Data Collection in SSMS 2008 is located?


Object Explorer - > Management - > Data Collection





 Process involved  in Data Collection:



SqlServer - Copy-Only Backups




Database – Restoration process: 


While restoring database , the differential database backups will have a reference to the full database backup , i.e through LSN (Log sequence number) .

If a full database backup is taken , any subsequent differential database backups
use this latest full database backup as their base .  


Hence ,database is restored with the help of sequence of LSN .
                 
LSN 's are stored with the backup file is to verify that the  backup files are being restored in the proper sequence .

Advertisements