SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: September 2011

Format Disk for SQL Server

Managing large SQL Server inventories requires standardisation. Do your research , create the scripts , and apply.

Occasionally, custom changes are required . This should be after careful testing.

One advantage of standardising is  for  Disaster Recovery. The rebuild is in the scripts.

If full server rebuilds are required… Read more

0 comments, 2,477 reads

Posted in SQLServer-DBA on 28 September 2011

ALTER AUTHORIZATION sql to change owner of a database

ALTER AUTHORIZATION changes the ownership of  entities .  Server level entity ownership can be changed to server- level principals. Database level ownership can be changed to database level principals.

ALTER AUTHORIZATION can change database ownership.  This replaces sp_changedbowner.  Alter any security management scripts using sp_changedbowner  - such as Attach database… Read more

2 comments, 73,165 reads

Posted in SQLServer-DBA on 27 September 2011

Performance Monitor Counters for SQL Server performance testing

I posted  Window Perfmon scripting, SQL Server perfmon and how to perfmon which refers to a performance monitor counters.

The  perfmon counters I use for SQL Server performance testing are listed below.

I place the list below into the perfmon_base_counters.cfg  file.

Note: some modifications need to occur… Read more

0 comments, 3,264 reads

Posted in SQLServer-DBA on 23 September 2011

SSIS version in SQL Server

Integration Services is the management interface for  SSIS packages. SQL Server uses  the “MsDtsSrvr.exe”  for executing packages

To highlight the point, configuring the firewall to access Integration Services requires allowing access to port 135 for the “MsDtsSrvr.exe”. 

Access the MsDtsSrvr.exe file to view the version number Read more

0 comments, 992 reads

Posted in SQLServer-DBA on 22 September 2011

Migrating to SQL Server from another Database platform

Migrating to SQL Server from another Database platform has a number of considerations

1)   Create an inventory of existing applications. Separate into off-the-shelf and in-house

2)   Ensure sufficient licenses on existing database platform can be retired to enable maintenance costs to decrease.

3)   For off-the-shelf .

      Does the vendor support … Read more

1 comments, 1,034 reads

Posted in SQLServer-DBA on 20 September 2011

Import Data from AS400 to SQL Server

The process to import data from AS400  uses a SQL Server  Linked server. The Linked Server points to an ODBC Connection.

 The ODBC connection (DSN) uses drivers to enable the connection between SQL Server and AS/400.

 Once the connection is established , t-sql statements extract data from the AS400 files. Read more

2 comments, 12,124 reads

Posted in SQLServer-DBA on 16 September 2011

Powershell , Excel charts and data presentation

The topic for T-SQL Tuesday is Data Presentation. Powershell to Excel is a a good way to presenting data, and works with SQL Server. Check my post Powershell sql server security audit  for other applications of Powershell and Excel

Data Presentation doesn’t just mean presenting data to the client… Read more

1 comments, 3,063 reads

Posted in SQLServer-DBA on 13 September 2011

SQL Server Quiz – the last time a stored procedure was executed

A developer rushes up to you and asks "can you tell me the last time this stored procedure was executed?". Your know that a number of methods can flush the cache , so you have to act quickly.

What method , with specific details , can you use to report… Read more

0 comments, 1,058 reads

Posted in SQLServer-DBA on 13 September 2011

TPC-H generate test data , test queries and sql database benchmark

 This is a followup on  my  earlier post of SQL Server test data generation testing tools.

I had some requests for my set up process notes for TPC-H . Includes : Installation , Import Data, Generate Queries 


 Install SQL Server

Download the TPCH   install files… Read more

3 comments, 5,222 reads

Posted in SQLServer-DBA on 8 September 2011

Database Cost Savings

I watched a video recently by Forester Research  , discussing Database Cost Savings. The video was intended for IT decision makers, Senior DBAs and systems managers. There were some interesting points  but all require careful analysis before implementation

The main message was : the current economic environment requires rethinking of… Read more

0 comments, 801 reads

Posted in SQLServer-DBA on 5 September 2011

Server sudden shutdown unavailable

It’s not always straightforward to diagnose a server problem. You look through the SQL Server Logs and Event Viewer for clues . There aren’t any obvious clues.

The steps I follow are:

1)       Is it a SQL Server problem?Is it an OS shutdown ? Is it a hardware problem?

2)       Write… Read more

0 comments, 949 reads

Posted in SQLServer-DBA on 3 September 2011

SQL Server test data generation testing tools

Question:  How do I generate test data for SQL Server ?  I don’t want to buy a benchmarking tool, but I  require a representative test case with real SQL statements.  How do the professional DBA’s capture SQL Server workloads for testing?

Answer:  First, try to use real-world data, not… Read more

0 comments, 865 reads

Posted in SQLServer-DBA on 2 September 2011