Interview Question of the Week #026 – 64 Bit Vs 32 Bit Confusion

I often see people getting lost between 64 bit and 32 bit operating systems and application conversation. There are plenty of individuals still today, not sure what is the difference between them as well what runs on what platform. Here are a few very simple questions related to Windows OS and SQL Server.

32bit 64bit Interview Question of the Week #026   64 Bit Vs 32 Bit Confusion

Question: Can you run SQL SERVER 32 bit on Windows 32 bit OS?

Answer: Yes

Question: Can you run SQL SERVER 64 bit on Windows 32 bit OS?

Answer: No

Question: Can you run SQL SERVER 32 bit on Windows 64 bit OS?

Answer: Yes

Question: Can you run SQL SERVER 64 bit on Windows 64 bit OS?

Answer: Yes

I guess there you go. In a simple words, you can run pretty much everything on Windows 64 bit OS. However, Windows 32 bit OS can’t run SQL Server 64 bit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server – 2016 – New Feature: Dynamic Data Masking

As most of you know that latest update from Microsoft about SQL Server 2016 was released of CTP2.1 version. There is a long list of new features getting introduced in SQL Server 2016. In this blog, I would talk about one of the security feature called Dynamic Data Masking.

Whenever you access your account on your bank site, would you be comfortable in seeing your credit card or bank account number in clear text on the web page? There are multiple ways to do this at the application level, but as a human nature, it leaves room for error. One small mistake from the developer can leak sensitive data and can cost a huge loss. Wouldn’t it be great if a credit card number would be returned with only its last 4 digits visible – XXXX-XXXX-XXXX-1234 with no additional coding? Sounds interesting, read on!

Before experimenting this feature please remember that if you are using CTP2.0 then you need to turn on trace flags using below command.

DBCC TRACEON(209,219,-1)

If you don’t enable, then here is the error which you would receive while trying my sample script given later.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘masked’.

Not to forget that this is SQL Server 2016 feature. Running the script on earlier version of SQL would cause below

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘MASKED’.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Here is the script to create objects (database, table, masked column)

SET NOCOUNT ON
GO
-- drop database MaskingDemo - if already exists
USE [master]
GO
IF DB_ID('MaskingDemo') IS NOT NULL
BEGIN
ALTER DATABASE
[MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE
[MaskingDemo]
END
-- create new database called MaskingDemo
CREATE DATABASE MaskingDemo
GO
USE MaskingDemo
GO
-- Create table with different data type columns
CREATE TABLE MyContacts (
ID INT IDENTITY(1, 1) PRIMARY KEY
,fName NVARCHAR(30) NOT NULL
,
lName NVARCHAR(30) NOT NULL
,
CreditCard VARCHAR(20) NULL
,
SalaryINR INT NULL
,
OfficeEmail NVARCHAR(60) NULL
,
PersonalEmail NVARCHAR(60) NULL
,
SomeDate DATETIME NULL
)
-- insert a row
INSERT INTO [dbo].[MyContacts]
([fName],[lName] ,[CreditCard],[SalaryINR],[OfficeEmail],[PersonalEmail], SomeDate)
VALUES('Pinal','Dave','1234-5678-1234-5678',999999,'pinal@SQLAuthority.com','pinal@foobar.in', '31-March-2013')
GO
-- apply masking
ALTER TABLE MyContacts
ALTER COLUMN CreditCard ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-XX",2)')
ALTER TABLE MyContacts
ALTER COLUMN SalaryINR ADD MASKED
WITH (FUNCTION = 'default()')      -- default on int
ALTER TABLE MyContacts
ALTER COLUMN SomeDate ADD MASKED
WITH (FUNCTION = 'default()')      -- default on date
ALTER TABLE MyContacts
ALTER COLUMN fname ADD MASKED
WITH (FUNCTION = 'default()')      -- default on varchar
ALTER TABLE MyContacts
ALTER COLUMN OfficeEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO
-- create a new user and grant select permissions
USE MaskingDemo
GO
CREATE USER WhoAmI WITHOUT LOGIN;
GRANT SELECT ON MyContacts TO WhoAmI;

I have added additional comments in script so that it’s easy to understand. Below script would show the power of masking.

USE MaskingDemo
GO
SELECT * FROM MyContacts; -- this would show clear data
GO
EXECUTE AS USER = 'WhoAmI';
SELECT * FROM MyContacts;     -- this should show masked data
REVERT;
GO

mask 01 SQL Server   2016   New Feature: Dynamic Data Masking

As we can see above, those fields which are masked are showing obfuscated data based on masking rule.
Note: Versions after CTP2 release, the trace flag will not be needed. If you add trace flag, you would start getting “Incorrect syntax” error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Tips working with Files inside SQL Server

When two SQL enthusiasts meet, often the conversation drifts to questioning each others understanding and implementation. Now, when two DBA’s meet it is always a great to see how the conversation goes because each carry a ton of baggage which can sometimes contradict too. One of the common rules of thumb has been to placing log and data files of different drives.

Recently, I had a friend say there is nothing much to do apart from placement of the drives. In one such conversation, I said – “Hey, it is not that simple. Isn’t there more to it as a DBA you take care, when working with your database files (data / log)?”. I got a blank look and was asked, Pinal I don’t see you have ever written anything like this over the blog. Though they are spread across over multiple blog posts, I thought this blog will aggregate some of the points I shared in the conversation.

#1 Tip

Physically isolate Data files from Log files. Combining heterogeneous workloads (workloads with very different I/O and latency requirements) can have negative effects on overall performance. Here is a KB article that talks about it in detail: Separate database and transaction log files on different drives for optimal performance and disaster recovery

#2 Tip

Secondly, it is highly recommended to place the database files and backups on separate physical devices. Otherwise, if the device that contains the database fails, your backups will be unavailable as well. Furthermore, placing the data (meaning Data and Log files) and backups on isolated devices optimizes the I/O performance for both production use of the database and writing backups.

#3 Tip

Similarly, it is strongly recommended to place the database files and FILESTREAM data on separate devices. Placing the data (meaning Data and Log files) and FILESTREAM data on isolated devices optimizes the I/O performance and provides better management of FILESTREAM data, namely regularly defragmenting such volumes. Just incase you want to learn more about FILESTREAMs, I recommend you read these older posts.

#4 Tip

Data files should be of equal size within each Filegroup, as SQL Server uses a proportional fill algorithm that favors allocations in files with more free space. You may recognize this recommendation as directed at the TempDB, but it applies to all user databases also. Read more about this from MSDN under File and Filegroup Fill Strategy section.

#5 Tip

Do not change the default file extensions (mdf, ldf and ndf) for data and log files. When talking about this, I would like you to take a look at the post: SQL SERVER – Antivirus Exclusions Best Practices With SQL Server because if any of these are changed, we need to make these exclusions accordingly. I am generally of the opinion not to disturb defaults unless we have a valid reason to do the same.

#6 Tip

Use the Instant File Initialization feature as this will allow a boost in performance, because when extending or creating a data file (does not apply to log files), it will not be zeroed right away, saving time and I/O cycles. This can be accomplished by granting the “Perform volume maintenance tasks” permission to the SQL Server account, which is set in the security policy console (secpol.msc). In this context I am reminded of a post by my good friend Tim Radney here which I attach for reference.

This permission does come with a small security risk, because by not zeroing out the existing space, when deleting data for example, there is a possibility that data could still be read, even though it has been “deleted”, until some other data writes on that specific area of the data file. However, the performance benefits outweighs the security risk and hence the reason for this recommendation.

As I wrap up, I think I have nicely tabulated some of the conversation points in this blog. I would love to hear from you on some of the recommendations you have done in your environments that could be a great addition to this blog via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How Do We Find Deadlocks? – Notes from the Field #086

[Note from Pinal]: This is an 86th episode of Notes from the Fields series. Deadlock is very annoying when they happen in our database. In my early career my biggest frustration was I had no idea why the deadlocks are happening and because I did not know the root cause, I was never able to solve them. As time passed by, now I have a better understanding of this situation.

JohnSterrett SQL SERVER   How Do We Find Deadlocks?   Notes from the Field #086

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting subject of how to find deadlocks in SQL Server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


For one reason or another I tend to find out that DBAs in the field have a hard time detecting and understand how deadlocks occur.  A deadlock occurs when two or more sessions are blocked on each other and one statement has to be terminated to prevent the sessions from being hung on each other.

Let’s take a quick look at locking, blocking and deadlocks with a real-world example that is included in the video below. In the simple example provided in the video there are two sessions each with statements that update the same tables concurrently with explicit transactions that are held until the statements are committed or rollback. For example, session one updates all the records in table one while session two updates all the records in table two. At this point we have locking. Session one has a lock on table one and session two has a lock on table two, but neither session is blocking each other from doing work.  Next while our statements are not committed in either session we have session one update the same records being updated on session two. This is known as blocking because session two is blocking session ones update table two.  Next we have session two attempting to update table one which is currently being locked by session one. Now we have a deadlock because session one is blocked by session two and session two is blocked by session one. To prevent both sessions from being hung one of them becomes the deadlock victim while the other get to continue its work.

How Do We Find Deadlocks?

Before SQL Server 2008 we would have to rely on configuring trace flags, service broker, or a server side traces to capture deadlock information so we could be proactive and take action to prevent the deadlocks from reoccurring. In SQL Server 2008 we were given extended events and could leverage a default system health extended event which is automatically running in the background to identify deadlocks. This is a great addition because we didn’t have to manually configure anything to catch the deadlocks. In SQL Server 2012 this got easier because we could rely on the graphical user interface inside of SQL Server Management Studio to filter our system health extended event to focus straight on our deadlocks.

In the video below you will be able to create deadlocks and be able to proactively monitor them with the native tools provided with SQL Server Management Studio in SQL Server 2012 or 2014.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine

Working with Excel files can always be of great interest. I have not seen a reporting solution in the world that doesn’t give an option to export to excel. If you are talking about two separate solutions and you want to export and import, then such options can be of great use. In this blog, I was trying to import data from Excel sheet to SQL Instance by using Import Wizard available in SQL Server Management Studio. I am sure all of us have had this requirement once in a while and would have surely tried to get this working.

ace error 01 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

After selecting the option, I selected Data Source as “Microsoft Excel” and selected file name which I wanted to import. Excel file was created with latest version of excel so I selected “Microsoft Excel 2007”.

ace error 02 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

As soon as I hit next, I got below error

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine. (System.Data)
——————————
BUTTONS:
OK
——————————

ace error 03 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

I checked management studio and as the message says, I didn’t see the provider as we can see below. We can also use below query to find the details of providers installed on the machine

EXECUTE MASTER.dbo.xp_enum_oledb_providers

ace error 04 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

Few blogs have mentioned to download https://www.microsoft.com/en-us/download/details.aspx?id=23734 (2007 Office System Driver: Data Connectivity Components) but that didn’t work. So I tried installing http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Microsoft Access Database Engine 2010 Redistributable) but it also failed because I am using 32 bit version of office on my 64 bit PC.

Note: If you are running 32-bit version of SQL then your issue will be solved by installing above. In my PC, I have to take below approach.

  1. Uninstall 32-bit version of Microsoft Office.
  2. Install the 64-bit version of MS Office (Make sure setup is run from “x64″ folder and run setup.exe in order to install the 64 bit of MS Office).
  3. Install the 64-bit version “Microsoft.ACE.OLEDB.12.0″ (download AccessDatabaseEngine_x64.exe from second link provided earlier).

Here is the location of DLL “ACEOLEDB.DLL”

  1. On a 32-bit operating system
    1. C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
  2. On a 64-bit operating system
    1. If 32-bit office is installed then

C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

  1. If 64-bit office is installed then

C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

Production Server would not have complete office installed so it would not be a big deal to install correct version of driver.

Have you ever encountered this error in your environments? What did you do to solve them? Let me know.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – PowerShell to Count Number of VLFs in SQL Server

If you are a seasoned DBA, then the conversations and monitoring VLFs is something you are already doing. To learn about basics of VLF, check my earlier blog at: SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Having large number of VLFs have a performance impact and there are a number of blogs that discuss the same. As a DBA in this blog I thought of showing a simple script that will help identifying the number of VLFs available inside a SQL Server instance or a number of instances.

As part of my powershell learnings, this is a simple script that you can use in your environment and take corrective action if you have large number of VLFs in your servers.

<#
EXAMPLE
   Get-VLFCount -ComputerName ComputerName
EXAMPLE
   Get-Content D:\ServerList.txt | Get-VLFCount

   If you are using Windows Authentication and have a list of servers you can use this.
   Get-VLFCount -ComputerName ComputerName1, ComputerName2
#>
function Get-VLFCount
{
    [CmdletBinding()]
    Param
    (
        # SQLInstance is the name(s) of the SQL Instances to scan
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$true,
                   Position=0)]
        $SQLInstance
    )
    Begin
    {
        #Load the Assembly to connect to the SQL Instance.
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

    }
    Process
    {
        ForEach ($Instance in $SQLInstance)
        {
            $SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
            $SrvConn.ServerInstance=$Instance
            #Use Integrated Authentication
            $SrvConn.LoginSecure = $true
            $SrvConn.ConnectTimeout = 5
            Write-Debug "Attempting to check $Instance for VLF Counts"
            $srv = new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)
            $dbs = $srv.Databases
            try
            {
                ForEach ($db in $dbs)
                {
                    Write-Debug "Getting VLFInfo for $db"
                    if ($db.IsAccessible)
                    {

                        $VLFs = $db.ExecuteWithResults("DBCC LOGINFO")
                        $NumVLFs = $VLFs.Tables[0].Rows.count

                        $VLFinfo = $db | Select @{Name='Instance Name'; expression={$Instance}}, @{Name='Database Name'; Expression = {$_.name}} `
                        , @{Name='VLFCount()'; Expression={$NumVLFs}}

                    }
                    else
                    {
                        $VLFInfo = New-Object psobject
                        $VLFInfo | Add-Member -type NoteProperty -name InstanceName ($Instance)
                        $VLFInfo | Add-Member -type NoteProperty -name DBName ("$DB is Inaccessible")
                        $VLFInfo | Add-Member -type NoteProperty -name VLFCount 0
                    }
                    Write-Output $VLFinfo
                }

            }
            catch
            {
                $ex = $_.Exception
                Write-Debug "$ex.Message"
                Write-Error "Could not pull SQL DB Info on $Instance"
            }

        }
    }
}

#If we had our instances in a file, we can use
Get-VLFCount 'localhost'

The above script when run on my server returns the following:

PS VLF Count 01 SQL SERVER   PowerShell to Count Number of VLFs in SQL Server

As you can see in our example output, our AdventureWorks database has large number of VLFs. Incase you have hundreds of VLFs in your environments, then you can look at SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file to reduce the number of VLFs for your databases.

Just out of curiosity, I would love to know what is the highest number of VLF in your databases? How long have these been running in your systems? It would be great if you can share the same with us over comments so that it would a great learning for me too.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?

Sometimes we are in a hurry that we forget activities that are pending. A typical example would be like walking into a room and we keep thinking about why we were there in the first place. Isn’t that common? Or I think age is catching on me for sure. Some of the classic mistakes I have seen people doing is to bring up an environment in a hurry and choose the Evaluation Edition because they don’t have the key handy at that point in time. They plan to add the same at a later point in time and forget it. One fine day SQL starts to error out and on inspection, you hit the problem and all work comes to a stop.

If you have been running SQL Server and it is expired, there would be a need to activate it. The steps are a little different as compared to Windows activation. The edition terminology is also little different from the Windows world. In SQL Server, evaluation edition is actually an enterprise edition of SQL Server, having 180 days’ time limit – so it is called as Enterprise Evaluation Edition

If the SQL Server engine is expired, the SQL services would not start and you will find below error in SQL Server error log and event viewer. Here is the blog to find a SQL ERRORLOG file location

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is the sample ERRORLOG. I have highlighted two things below – current version and error message. The same message will come in event log also.

2015-03-25 10:34:06.84 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
2015-03-25 10:34:06.84 Server      (c) Microsoft Corporation.
2015-03-25 10:34:06.84 Server      All rights reserved.
2015-03-25 10:34:06.84 Server      Server process ID is 6584.
2015-03-25 10:34:06.84 Server      Authentication mode is MIXED.
2015-03-25 10:34:06.84 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2015-03-25 10:34:06.84 Server      The service account is 'NT Service\MSSQL$MSSQLSERVER'. This is an informational message; no user action is required.
2015-03-25 10:34:06.84 Server      Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2015-03-25 10:34:06.84 Server      Command Line Startup Parameters:
-s "MSSQLSERVER"
2015-03-25 10:34:07.15 Server      Error: 17051, Severity: 16, State: 1.
2015-03-25 10:34:07.15 Server      SQL Server evaluation period has expired.

On the other hand, if client tools are expired, then we would have trouble opening SQL Server Management Studio and other client tools. Here is the pop-up which would be received.

Evaluation period has expired. For information on how to upgrade your evaluation software, please go to http://www.microsoft.com/sql/howtobuy

sql expired 01 SQL SERVER   Evaluation Period Has Expired   How to Activate SQL Server?

In such situations, we need to perform an activation of SQL Server. This is called as edition upgrade in SQL Server installation terminology. To upgrade, you need to have installation media of the destination version. SQL Server evaluation edition can be upgraded to standard, developer, enterprise editions. The complete upgrade metrics can be found in SQL Server books online https://msdn.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

Here are the steps:

  1. From SQL Server installation media, launch setup.exe.
  2. From the SQL Server Installation Center click Maintenance, and then select Edition Upgrade

sql expired 02 SQL SERVER   Evaluation Period Has Expired   How to Activate SQL Server?

  1. It would launch Wizard where you need to keep on pressing next, next and finally “Upgrade”. Make sure that edition shown in the license terms screen is what you purchased.

Hope this would help.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #025 – How to Implement Paging in SQL Server?

Here is a very popular question when SQL Server interview consists of live coding example.

Question: How to implement paging in SQL Server? Explain with the help of script.

Answer: Here is the script to implemented paging in SQL Server 2005.

USE AdventureWorks
GO
DECLARE  @StartRow INT
DECLARE  
@EndRow INT
SET    
@StartRow = 120
SET @EndRow = 140
SELECT    FirstName, LastName, EmailAddress
FROM (
SELECT    PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM    Person.Contact PC) PersonContact
WHERE    RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

Above query is using ROW_NUMBER () function, but you can also use CTE to accomplish the same. Here is the script for the same:

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE
@EndRow INT
SET
@StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

You can read more about this in following blog posts:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

I have been getting my hands dirty working with PowerShell commands for a fun learning experience and few have made to this blog. The after effect of this is that I have been pinged for few queries on them too. Recently I got a call from one of my friend and he wanted to use PowerShell to connect to SQL and run some queries. Initially I thought that he is talking about SQL PowerShell, but his intention was to connect to SQL using SQL Server Native Client and run the query. That was strange but I was ready and up for the challenge in front of me. The curiosity is just too much that I couldn’t sleep till I found a solution to this challenge he put to me.

I wrote a small sample and sharing here for your benefit. First, we need to create a sample database and a table which would be used by PowerShell.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE EmployeeMaster (
EmployeeID INT
,FirstName VARCHAR(10)
,
LastName VARCHAR(10)
)
GO

Now, our task is to use PowerShell and insert data into EmployeeMaster. Here is the step by step PowerShell script.

# Make Connecting string to the SQL Server Instance and the Database
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.; Initial Catalog=SQLAuthority; Integrated Security=SSPI")
# Define variables and the values to be inserted
$EmployeeID = 1
$FirstName = 'Pinal'
$LastName = 'Dave'
# Open database connection
$con.Open()
#Create SQL Insert Statement for table EmployeeMaster with the values
$stmt_insert = "INSERT INTO [EmployeeMaster]
([EmployeeID],[FirstName],[LastName])
VALUES($EmployeeID ,'$FirstName' ,'$LastName')"
# Create command to execute to SQL connection
$cmd = $con.CreateCommand()
$cmd.CommandText = $stmt_insert
# Invoke the Insert statement
$cmd.ExecuteNonQuery()

Once we execute above, the data would be inserted into the table. Now, to fetch the data, I am going to use invoke-SQLCMD cmdlet

Add-PSSnapin SqlServerCmdletSnapin100
$DataSet = Invoke-Sqlcmd -Database "SQLAuthority" -Query "SELECT * FROM [EmployeeMaster]" -ServerInstance .
foreach ($element in $DataSet)
{
$element
}
# Close database Connection
$con.Close()

We can run all the statements together in one PowerShell script and use Windows PowerShell ISE to execute it.

sql ps sample 01 SQL SERVER   Using PowerShell and Native Client to run queries in SQL Server

You might get a few errors while using Invoke-SQLCMD which I have explained here

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

Hope this would give you a head start of using PowerShell to connect to SQL and run queries. This was a cool experiment for me and I thought was worth sharing. Have you ever had such requirements? Do share your experience over comments below.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Database Snapshots and Replica Causing Write Delays

For folks who have not worked with SQL Server Database snapshot feature, please refer to the blog I wrote sometime back (SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot). I am giving this reference because some might not be aware of this powerful capability with SQL Server.

In the above article, one of the readers did ask if there is any performance overhead of having multiple Database snapshots with SQL Server.  Well, database snapshots are built on the principle of COW (Copy On Write) where a database page is copied to the snapshots when they are written in the primary database. That is the whole idea of Database snapshots. Hence, if you have multiple database snapshots configured, for each write on the primary DB, we will be indirectly getting “n” more write based on the number of snapshots made on the DB. It is as simple as that.

In one of my customer locations, they were getting heavy performance issue on their writers and when I got an option to inspect, I found there were close to 6 database snapshots that were created in that database. When I asked, they said it is some sort of backup copy so that they can come back onto that version. Being a heavy transactional system, this showed up easily when the load was high.

The immediate question was, how can I identify we have far too many Snapshots created on the databases? I suggested a simple script so that they can start looking into it from time to time:

IF EXISTS( SELECT source_database_id
FROM sys.databases
WHERE source_database_id IS NOT NULL
GROUP BY source_database_id
HAVING COUNT(*) > 1)
AND EXISTS(
SELECT waiting_tasks_count FROM sys.dm_os_wait_stats
WHERE wait_type = 'replica_writes' AND waiting_tasks_count > 0)
BEGIN
SELECT
DB_NAME(source_database_id) AS 'SourceDB',
COUNT(*) AS 'NumSnapshots'
FROM sys.databases
WHERE source_database_id IS NOT NULL
GROUP BY DB_NAME(source_database_id)
HAVING COUNT(*) > 1
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'replica_writes' AND waiting_tasks_count > 0
END

In their environment this showed up, having 6 snapshots for their main transactional database and they were able to delete some of them based on age.

Our database systems are sensitive to such mistakes and can cause us huge performance overhead. The development team thought that the system was deteriorating over a period of time and were taking this performance overhead without analyzing what could be the problem. I am a firm believer that one should always know the root cause for a problem rather than just solving the problem superficially.

Do let me know if you use Snapshots actively in your environments and have you faced such issues in your SQL Server box? One of the key places where I have seen people use Database Snapshots is when Mirroring is configured and we want to query from our Mirror Server. Are there other places where you have relied on Database Snapshots? Let me know via your comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)