Fix of could not load file or assemby “Microsoft.SqlServer.sql, Version=………” Error

error-db-creation

If above error occurs while creation of new SQL Server Job or New database, then follow below step to overcome issue:

Just copy  file

“C:\Program Files (x86)\Microsoft SQL Server\110\Shared\Microsoft.SqlServer.Sqm.dll”

to

“C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio”

Note : C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio is the place where ssms.exe is placed. Generally, this dll file should exist in the same folder where ssms.exe exists”

Advertisements
Fix of could not load file or assemby “Microsoft.SqlServer.sql, Version=………” Error

Data file growth events and clustered index

Introduction

There are couple of regular activities those are performed by DBA. Performance tuning comes out of the box then. It requires a strong understanding of how different operations are played by SQL engine. One day, I got update from development team that their procedure is not working well and it is taking a lot of hours to get results. It was found that there is no index on primary table being used in query. I thought that it is just a matter of minutes for me to add clustered index on heap. I ran index creation query and got surprised when hard disk started running out of space by 1 GB every minute. I had to eventually kill process and cloud of questions formed in my brain that how did that happened.

Explanation

Following are reasons and recommendations which I got from a popular SQL Server forum:

Reason

  • There are around 10 data file growth events of around 1 GB.

https://thesqlguy.wordpress.com/2011/06/21/query-to-display-recent-data-and-tlog-file-autogrowth-and-autoshrink-events/

  • Create a clustered index on the heap my require up to twice the size of heap during building operation.

Recommendations

  • If TempDB is on different drive, you can possibly reduce the size requirements by using SORT_IN_TEMPDB = ON when creating index. That will move activity to different drive, reducing space overhead on destination drive.
  • You can shrink log file to get space. But this is very short term solution. In simple recovery mode, log files will grow to accommodate the size of largest transaction or current transactions. You can do this during maintenance window to temporarily free up space, but it will not solve problem. It is best to move log files to different drive as long as the log file space is not an issue.
  • Do not think of shrink data files to free up space. It may lead to fragmentation which negatively affects performance.
  • Drop any Non clustered index before adding clustered index. As those NC index will also need to be rebuild and it requires space for the same. After clustered index is created, add non clustered index again. (This is maintenance window task, use will experience performance impacts)
Data file growth events and clustered index

SQL Server database Migration recommendations

Introduction

Database migration is one of the core responsibilities of SQL Server DBA. Couple of applications in a company may be hosted on environment of which Microsoft will be ending support in near future. Hence, it is likely for project team to do application migration with in a good time frame. This process needs lot of meetings and brain storming session to take decision. Database driven application are more likely to have database movement from old servers to new host server.

There could be following possible decision which can be taken for database perspective:

  • Move old database (version 2005) host on server 2003 to a new database instance (version 2012) without changing compatibility level to 110.  This is a best choice if your goal is to just eliminate old instances of SQL Server or application is complex and time frame is very less to wrap up your development work.
  • Move old database (version 2005) host on server 2003 to a new database instance (version 2012) with compatibility level changed to 110. This is a best choice if your team has a time to rewrite development code if required and you want to utilize latest functionalities of SQL Server in application.

With whatever reason you are going on, it is not just that simple. There are chances that you can miss important steps while migrating database.

Recommendations

Following are the recommendations while working on database migration process:

  • Prepare a list of all databases host on same server and are interlinked with the database (one application can user multiple databases) which you are going to migrate.
  • Compatibility Test 1 – Use Microsoft® SQL Server® 2012 Upgrade Advisor tool against all listed databases to collect incompatibility report with respect to SQL Server 2012. Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. On the basis of report, you need to decide about the amount of work hours that you need to invest before and after migration of databases. There may be couple of deprecation announcement or deprecated feature being used in current application and those code features should be removed from SQL code.

SQL Server Upgrade Advisor (SSUA)—This Microsoft tool analyzes an existing older version of a SQL Server database and identifies any issues with existing objects. For example, the SSUA tool will identify any stored procedures that use old and deprecated syntax. This tool currently tops out at SQL Server 2008 R2, but it’s still very useful. To learn more about this tool, see the Microsoft “Use Upgrade Advisor to Prepare for Upgrades” page.

<http://sqlmag.com/sql-server-2012/upgrade-assistant-sql-server-2012-uafs-and-sql-server-upgrade-advisor>

  • Compatibility Test 2-If you have statement level trace files (24*7 traces capturing each query running on your SQL instance) on old SQL Server, you should also run them against Microsoft® SQL Server® 2012 Upgrade Advisor to get information about all deprecated query features which have been used as ADHOC query on application level code.
  • Collect properties of each database like
    • Auto Stats
    • DB Owner
    • Recovery Mode
    • Compatibility level
    • Trustworthy option
    • SQL Logins
    • SQL Jobs
    • Maintenance plans and activities
    • Database Size
    • Data and log file location
    • Information of dependent applications
    • Linked Servers
  • Performance Test – Performance testing is also necessary for complete migration. Because, at last all database will also get migrate to new instance. Just do not assume that new version will perform better. New version might be running legacy code that isn’t efficient or won’t benefit for new functionality in new server. Best option is to prepare a testing VM machine with same configuration as that of current database environment. Once, machine is ready , get latest full backups of each database and restore them on ready targeted test VM machine. Make connection of test applications to this test database instance and start capturing 24*7 profiler trace at query/statement level. Run those captured instances against Upgrade Assistance tool.

Upgrade Assistant for SQL Server 2012 (UAFS)—This tool, which is written by Scalability Experts and Microsoft, incorporates SQL Server 2012’s new distributed replay feature to incorporate workload testing to identify compatibility issues that exist in an application’s source code, such as an ASP.NET application, rather than inside the database. Because this tool is specially designed for SQL Server 2012, we’ll focus on it in this article.

<http://sqlmag.com/sql-server-2012/upgrade-assistant-sql-server-2012-uafs-and-sql-server-upgrade-advisor>

Quickly and easily determines problems that might slow or prevent upgrading from an existing SQL Server database to SQL Server 2012

  • Once performance testing is completed and results are compiled, start restoring backups on target server and if your team has decided to use latest feature version and have time to invest, then do change compatibility level from 90 to 110 after restore. If your team want to move databases to newer instance of SQL Server but at the same time have it backward compatible, you need to make sure that the compatibility stays the same. Do not change compatibility level to 110.

(Note – Compatibility level does not automatically change when we upgrade a database from older version into latest database engine. This is not a major problem, but there are certain features that we will not be able to take advantage of unless we change compatibility level to latest one available)

  •  Transfer logins to new server and fix orphaned users.
  • Transfer SQL Jobs to new server
  • Add changes on the basis of performance test report. Like updating Statistics of all tables and indexes, recompiling all procedures.
SQL Server database Migration recommendations

Capture Connections to any SQL Server database

Scenario

As a member of project migration team, you are asked by your project manager to track all the connections coming to a particular database.

Solution

Solution in setting a server side trace. Server side trace is one of the automated task of DBA which runs continuously at background and collects number of events in a trace file (.trc). Trace file is the only option which is available to server side tracing. Contrary, we can save trace information in table also which is available in profiler tracing. This solution will show you how to set up an automatic 24*7 trace, archiving trace files and transferring data into SQL Server table. Following are steps of provided solution.

  1. Fetch trace definition from profiler
  2. Create SQL Server Job which performs following tasks and schedule it hourly/half hourly.
    1. Stops current trace
    2. Archives current trace to archival folder
    3. Starts a new trace
  3. Write a code to read all trace files into a folder

Schema creation

/****** Object:  Table [dbo].[MigrationFile]    Script Date: 12/09/2016 06:34:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[MigrationFile](

[Path] [varchar](255) NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

CREATE TABLE [dbo].[MigrationTrace](

[LoginName] [varchar](500) NULL,

[ApplicationName] [varchar](500) NULL,

[NTUserName] [varchar](500) NULL,

[StartTime] [datetime] NULL,

[HostName] [varchar](500) NULL

) ON [PRIMARY]

Fetch trace definition from profiler

  1. Open SSMS, go to Tools and open SQL Server Profile.
  2. Connect to any server
  3. Go to Events Selection tab and select Audit Login event. Deselect other events as those are not required right now.
  4. Hit Run button at the bottom.
  5. Stop the trace.

  6. Open File Menu > Export > Script Trace Definition>For SQL Server 2005-2014 and Save the definition SQL and copy code from the same.

SQL Server Job Steps

Stop Current Trace Code – 1st Step of SQL Job Login Trace

— get trace_id

declare @trace_id INT

select @trace_id = id

from sys.traces

where path LIKE ‘C:\Traces\LoginTrace%’

IF @trace_id <> 0

BEGIN

print ‘stopping’

— Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

— Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END

 

SQL Server Job Step -2

      Rename and Archive current trace- 2nd Step of SQL Job Login Trace

                     –declare variables

declare @server varchar(30), @date char(14), @file char(100), @cmd varchar(250), @path varchar(255)

select @server = REPLACE(UPPER(@@servername),’\’,’_’)

select @date = replace(replace(replace(convert(char(19),getdate(),120),’:’,”),’-‘,”),’ ‘,”)

select @file = @server+’_’+@date+’.trc’

— change trace file name

select @path = path

from MigrationFile

select @cmd = ‘RENAME ‘+@path+’ ‘+@file

–exec master..xp_cmdshell @cmd

— move trace file to \archive

select @cmd = ‘MOVE C:\Traces\*.trc L:\Traces\archive’

exec master..xp_cmdshell @cmd

delete from MigrationFile

SQL Server Job Step -3

Re-run trace

/****************************************************/

/* Created by: SQL Server 2014 Profiler          */

/* Date: 12/09/2016  04:10:26 PM         */

/****************************************************/

 

 

— Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @filepath nvarchar(255)

 

set @maxfilesize = 5

set @filepath = ”

select @filepath = @filepath + N’C:\Traces\LoginTrace’ + ‘_’ + convert(varchar(12),getdate(),102)+’.’+replace(convert(varchar(12),getdate(),108), ‘:’, ‘.’)

 

— Please replace the text InsertFileNameHere, with an appropriate

— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

— will be appended to the filename automatically. If you are writing from

— remote server to local drive, please use UNC path and make sure server has

— write access to your network share

 

/*Save File Name for future*/

insert into MigrationFile(path)

select @filepath+’.trc’

 

 

exec @rc = sp_trace_create @TraceID output, 0, @filepath ‘, @maxfilesize, NULL

if (@rc != 0) goto error

 

— Client side File and Table cannot be scripted

 

— Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 14, 1, @on

exec sp_trace_setevent @TraceID, 14, 9, @on

exec sp_trace_setevent @TraceID, 14, 10, @on

exec sp_trace_setevent @TraceID, 14, 11, @on

exec sp_trace_setevent @TraceID, 14, 6, @on

exec sp_trace_setevent @TraceID, 14, 8, @on

exec sp_trace_setevent @TraceID, 14, 12, @on

exec sp_trace_setevent @TraceID, 14, 14, @on

exec sp_trace_setevent @TraceID, 14, 35, @on

 

 

— Set the Filters

declare @intfilter int

declare @bigintfilter bigint

 

exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler – 28055c81-f01c-43a0-b0be-3565d746f941’

— Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

 

— display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

 

finish:

go

Schedule Job hourly…………….

Capture Connections to any SQL Server database

List SQL Server Objects (Stored Proc, Function, Trigger, View) with some text

DECLARE @SEARCHSTRING VARCHAR(255)
	,@notcontain VARCHAR(255)

SELECT @SEARCHSTRING = 'article'
	,@notcontain = ''

SELECT DISTINCT sysobjects.NAME AS [Object Name]
	,CASE 
		WHEN sysobjects.xtype = 'P'
			THEN 'Stored Proc'
		WHEN sysobjects.xtype = 'TF'
			THEN 'Function'
		WHEN sysobjects.xtype = 'TR'
			THEN 'Trigger'
		WHEN sysobjects.xtype = 'V'
			THEN 'View'
		END AS [Object Type]
FROM sysobjects
	,syscomments
WHERE sysobjects.id = syscomments.id
	AND sysobjects.type IN (
		'P'
		,'TF'
		,'TR'
		,'V'
		)
	AND sysobjects.category = 0
	AND CHARINDEX(@SEARCHSTRING, syscomments.TEXT) > 0
	AND (
		(
			CHARINDEX(@notcontain, syscomments.TEXT) = 0
			OR CHARINDEX(@notcontain, syscomments.TEXT) <> 0
			)
		)


proc-text
List SQL Server Objects (Stored Proc, Function, Trigger, View) with some text

Surrogate Key

An additional column added into a table to uniquely identify each row having non business value

  • Its values are automatically generated at the time of record insertion
  • Its values are not known to end users i.e. these are not available in end user reports
  • It has full potential to become a primary key into a table due to its unique data
  • It is useful when your records have no natural key ( such as Person table)

MS SQL Server uses IDENTITY to generate surrogate key data. When used a primary key, it is liable to use this key as foreign key to make relationship between table.

Pros :

  • Business data is independent from this key. Hence, any change in other keys (business columns) would not lead to change primary and foreign key relationships. Because, primary and foreign keys are surrogate values and have no business value.
  • Smaller in size ( Integer, most of the time). Hence, smaller index.
  • Easy to guarantee uniqueness.

Cons:

  • Not useful while searching data (No business value)
  • Separate indexes are required on natural data columns when surrogate key is used a primary key.
Surrogate Key