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,407 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, 71,341 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,225 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, 970 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,025 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, 11,924 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,010 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,036 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
Installation
Install SQL Server
Download the TPCH install files… Read more
3 comments, 5,128 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, 770 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, 930 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, 860 reads
Posted in SQLServer-DBA on 2 September 2011