Off the shelf software and installation prerequisites

Companies purchase off the shelf  software to cover all aspects of the business. I’m including: Payroll, Web Filtering, Security audits,Monitoring, Bookeeping, modelling .. the list is endless. Many of these products are dependant on databases.  

The typical situation is a list of instructions on how to make environment changes, security changes , DDL to create the database with objects.

The Installation Prerequiste needs a special mention. The vendor installation guide  has information relevant to the database creation. I’m amazed by some of the recommendations. Some examples recently:

1)       Autogrowth of data and log files at 50% . Read more on SQL LOG FILE AUTOGROW performance troubleshooting

2)       DBCC SHRINKDATABASE every night

3)       Logon mapped to database user is set with sysadmin privileges

 None of the 3 examples  fit into any  best practises . At this point I contact the vendor , explain the situation and they normally agree but with a caveat “they’ll take no responsibility if there is a product issue”.

Why does this situation arise?

 Most vendors release products on certain assumptions

1)       There is no expert DBA or IT support in the customer company

2)       Expectation of system to run unattended

3)       No knowledge of customer network

4)       No knowledge of customer hardware

A quality DBA will identify problems ,  often associated with background tasks or maintenance procedures. They will phone the vendor , who will recommend some adjustments

It should be a minimum requirement for all vendors to supply a detailed FAQ for DBAs to manage the system. The in-house DBA is likely to have a standardised approach customised to the system.  Integrating off the shelf software causes unnecessary overhead if every recommendation is followed

In some cases the recommendations are justified and explained in full, but that’s an exception , not the standard level of documentation.

Read More

SQL Server Performance Killers - SQL Server DBA

Write everything down during problem ... - SQL Server DBA

SQL Server – DBA tactics

SQL Server - Find Default Data location and Default Log Location

Finding the  SQL Server default data and log location  (or Database default Locations ) is useful. When a CREATE DATABASE command is issued with target location paths explicitly stated , SQL Server will  create the data and log files in the default data and log locations.

That’s not always the intended result.   For example, the DBA may need to change the location of system files or move all databases due to disk issues or to improve database performance - read more on  SQL Server – files and filegroups. Improving database performance

Method 1 : Use SSMS to view Default Data and Log details

1. In the SSMS Object Explorer panel, right-click a server and click
2. Properties.
3. In the left panel,under Select A Page title,  click the Database settings page.
4. In Database default locations, view the current default locations for new data files and new log files.

 Method 2: T-SQL and xp_instance_regread

 

DECLARE @DataLoc nvarchar(2000);
DECLARE @LogLoc nvarchar(2000);

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData', 
    @DataLoc OUTPUT;

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog', 
    @LogLoc OUTPUT;

SELECT @DataLoc AS 'Default Data Location',
       @LogLoc AS 'Default Log Location';

 Read More

sql server scripts and managing databases - SQL Server DBA

Operations DBA description - SQL Server DBA

SQL Server 2005 and SQL Server 2008 Upgrade tasks

SQL Server – Tempdb move files to another drive - SQL Server DBA

SQL Server – Find SQL Server tcp port with Powershell

I developed some scripts to gather information for a Disaster Recovery preparation.  Finding and storing the port number used by a SQL Server Instance was a requirement. The SQL Servers are 2012 , which allowed the sys.dm_server_registry DMV  to be used.  To find earlier versions TCP port information , it is necessary to investigate the Windows registry keys

 The sys.dm_server_registry returns SQL Server Instance specific Windows registry details. One of the rows is “TcpPort”.

The Powershell Invoke-sqlcmd cmdlet is useful when using T-SQL or XQuery and  sqlcmd is the preferred method of connecting to the SQL Server.

 

Invoke-sqlcmd -ServerInstance "MyServer\MyInstance" -Query "select value_data AS Port from sys.dm_server_registry WHERE value_name = 'TcpPort'"

 Related Posts

TCP Chimney offload - SQL Server DBA

Error 26073 and TCP connection closed - SQL Server DBA

Powershell and Disaster Recovery preparation – T-SQL

SQL Server - How to setup ODBC for IBM AS/400

Setting up an ODBC connection to AS400 is a requirement  for importing data into SQL Server. There are alternative methods for AS/400 DB2 connection. 

The method outlined in this post , uses the AS400 client to supply the drivers

 

Steps to setup ODBC connection to AS/400

Step 1)   Install the AS400 client on the server you wish to setup the ODBC connection.

 

Step 2)   Reboot  the server.

 

Step 3)  Setup the ODBC connection.

1. Start the ODBC Administration in the iseries programs

Start | IBM iSeries Access for Windows |ODBC Administation

• Select the System DSN tab
• Click on Add, Select iSeries Access ODBC Driver click on Finish
• Fill in the iSeries Access For Windows ODBC setup

Data source Name : This is the name of the AS400 server

The Connection options should “Use iSeries Navigator default,

The server tab

Naming Convention: System Naming convention (*SYS)

SQL default library: QGPL

Library List :Place  the name of the library for this ODBC connection

Connection type:  change to Limit to Read-Only (this is all that is required for this connection )

Packages Tab: Leave as default

Performance Tab: Leave as Default

Language Tab: Leave as default

Catalog Tab: Leave as Default

Translation Tab: Leave as Default

Diagnostic Tab : Leave as Default

 Click on OK, This will create the ODBC connection.

 

Step 4)  Create the linked server within the SQL Instance.

       Connect to the SQL instance that you have created the ODBC connection.

• Expand the Server Objects | Linked Servers

Right click on the Linked Server and select New Linked server

• Linked Server is the name of the Linked server (this will be used by any scripts to use the linked server)
• Provider : Microsoft OLE DB Provider for ODBC Drivers
• Product Name : AS400
• Data source : MYAS400 ( this is the name of the ODBC created in the first part of this Doc
• Catalog :  MYAS400 ( Also the same name as the AS400 server)

 

 

• ISecurity tab:
• Select Be made using this security context
  • Remote Login : <A valid as400 user>
  • With password : <The correct password for the user>

 

Click on Ok, this will check the connection and create the link server.

Read More

SQL Server Import Data from DB2 AS400 iSeries

Import Data from AS400 to SQL Server

SQL Server Linked Servers and User permissions

SQL Server – Check SQL Server 2008 Native Client is installed with Powershell get-wmiobject

Related Posts

Powershell scripts for DBA

Question: I want to check if the MS SQL Server 2008 Native Client is installed using Powershell. What Powershell cmdlet can I use to gather the information?

On the basis of the result returned by the Powershell cmdlet , I’ll prompt the use to install the MS SQL Server 2008 Native Client, before they proceed with the remaining actions.

Answer: The Powershell Get-WmiObject  can return the information. The  Get-WmiObject  is a great cmdlet for system administration.

 

Get-WmiObject  "win32_product" | select-object -property Name,PackageName,Version