ServerSide Blog

Some news and tidbits that we share

Viewing entries from SSTS
SSTS

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Rsync across a network to preserve permissions

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Thursday, 26 March 2015
Blog 0 Comments
Recently I had to copy a directory structure including a mysql database. The problem I ran into was permissions were not preserved (despite using the -a option). I then came across an option to sudo which preserved the permissions.
  • time rsync -avh -E --progress --rsync-path="sudo rsync" user@remote_hist:/mnt/database-backups/BAK
Tags: Untagged

Adding a new table to existing replication scheme in SQL Server

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Tuesday, 16 December 2014
Blog 0 Comments

In summary, need to add the article to the publication and then generate a new snapshot.

The link below contains the details:

http://blog.extreme-advice.com/2012/11/27/add-new-article-in-transactional-replication-in-sql-server-2012/

Tags: Untagged

Useful article regarding configuring the Listener on an AWS instance

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Tuesday, 03 June 2014
Blog 0 Comments

A common issue with running Oracle on AWS involves the listener and the lack of ip/name consistency across AWS instance restarts. The link below contains some good info along with a script that deals with the ip/name consistentcy problem.

http://survivalguides.wordpress.com/2012/07/29/starting-an-oracle-xe-listener-on-amazon-ec2-instance/

Tags: Untagged

BINARY_CHECKSUM() function in SQL Server

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Thursday, 26 December 2013
Blog 0 Comments

BINARY_CHECKSUM is a nifty SQL Server function that allows for a hash of the provided columns. This can come in handy when trying to look for differences in table column values. The function can receive a variable number of columns (or * for all columns) of a table. A great use of this would be in detecting change between values over time for the Dimensions in a Data Warehouse data model.

 

example usages:

select BINARY_CHECKSUM(*) from table

select BINARY_CHECKSUM(column1, column2) from table

Tags: Untagged

Web Based Diagramming tool

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Wednesday, 04 December 2013
Blog 0 Comments

This morning I was searching for a tool for basic diagramming to draw up an environment that includes several servers and a replication topology between them. I found a nice little tool at https://www.draw.io. No nonsense, as soon as you hit the link you are in the diagramming editor. Disgrams can be saved locally and exported to several format. They even have a plug-in for Confluence.

 

 

Tags: Untagged

SQL Server function to read SQL Profiler trace files (fn_trace_gettable)

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Wednesday, 07 August 2013
Blog 0 Comments

SQL Server Profiler creates files on the OS that can later be read in for analysis. 
The following parses a trace file and inserts the data into a table.
SELECT * INTO trace_folder
FROM::fn_trace_gettable('C:\mytrace.trc', 4)

Tags: Untagged

Function to determine primary in an Availability Group

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Friday, 26 July 2013
Blog 0 Comments

If you are using Availability Groups in SQL Server 2012 there are times that you may want to hinge some login on the current primary of the Availability Group. For example, you may want to run jobs against the primary of the Availability Group. This primary can vary over time so it would be to your advantage to be able to determine this in a dynamic way. Because of this I created a function to assist.

create function [AG].[is_ag_primary]() returns smallint

as

begin

declare @l_is_primary smallint

 

 

select @l_is_primary = max(primary_status) from (

select  case when replica_server_name=@@servername and role_desc='PRIMARY' then 1 else 0 end primary_status

from  sys.availability_replicas r

left join sys.dm_hadr_availability_replica_states rs on rs.replica_id = r.replica_id

and rs.role_desc ='PRIMARY'

) t

 

return @l_is_primary

end

GO

Tags: Untagged

mysqldump --tab with the mysqlimport utility for multi-threaded restore

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Friday, 19 July 2013
Blog 0 Comments

Recently I was trying to move a decent sized mysql database to a new server. The database was about 40GB and it was taking several hours to restore the database using the mysql client. Searching for faster options I came across an option to mysqldump that produces a fileset per table instead of one big file. Haing multiple files allows usage of mysqlimport. mysqlimport has a --use-threads option so if you are working on a host with multiple cpu's you can leverage them for a much faster import. In my case I had 80 cores at my disposal. This resulted in a 7 hour import reducing down to 37 minutes.

Commands used:

mysqldump --tab

mysqlimport --use-threads

Tags: Untagged

How to extract Postgres tables to csv files

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Tuesday, 18 June 2013
Blog 0 Comments

The Postgres COPY command can be run within psql to extract csv files for exchange to an external system. You supply

a file location, delimiter, and format.

Here's an example:

COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;

Tags: Untagged

Query to view Availability Groups Read Only routing list

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Thursday, 06 June 2013
Blog 0 Comments

In order to take advantage of SQL Server Availability Groups Read Only functionality you need to setup a routing list. The query below displays the current state of Read Only routing in an Availaibility Group

 

 

 

 

Tags: Untagged

Renaming a SQL Server instance

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Monday, 03 June 2013
Blog 0 Comments

Once in a while it may become necessary to rename an instance of SQL Server. Because this isn't something that you do frequently it's easy to forget the commands. It's just a couple of commands (see below) followed by a restart of SQL Server.

 

exec master.dbo.sp_dropserver ''

exec master.dbo.sp_addserver '', 'local'

 

now, Restart SQL Server

Tags: Untagged

SQL Server Change Data Capture (CDC) article

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Friday, 12 April 2013
Blog 0 Comments

Below is a great introduction to the Change Data capture functionality in SQL Server. CDC is a great way to capture data changes to your database without having to code your own triggers and logic. CDC reads the transaction log and so is asynchronous and minimizes contention within SQL Server. CDC can be configured as wide spread as the entire database or can be a specific as one column on one table in your database.

Check out the link below for an understanding on how to implement CDC.

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

Tags: Untagged

Change the port for Oracle Enterprise Manager

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Wednesday, 10 April 2013
Blog 0 Comments

If you have a reason to change the port that the EM web server is running try the command below (in this case changing to port 1158)

emca -reconfig ports -DBCONTROL_HTTP_PORT 1158

Tags: Untagged

Postgres - adding client access

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Thursday, 04 April 2013
Blog 0 Comments

Postgres by default does not allow all network clients access to the databases by simply providing a username and password. The clients must be "registered" via the pg_hba.conf file. See http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html for details. After changes are made to pg_hba.conf the server must be signaled to reload the configuration files using pg_ctl reload

 

Tags: Untagged

Creating new logins with SQL Server High Availability Groups

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Thursday, 10 January 2013
Blog 0 Comments

If you add new logins to your environment and want the associated database permissions to flow through to replicas you must create the logins with common sid identifiers. To do so query for the sid for the login from the syslogins table. After you have the sid you can create the login on the replicas as follows:

create login <name> with password='', sid=<sid>

After you do this database permissions will flow through from the primaries to the replicas and in the event of fail over users will have their appropriate access.

Tags: Untagged

Enabling snapshot isolation in SQL Server

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Tuesday, 09 October 2012
Blog 0 Comments

Snapshot Isolation allows for readers to access data without blocking writers. This is more like how Oracle works and can improve overall throughput of your system. Keep in mind that snapshot isolation will place greater demands on tempdb as before images of data will be stored there in order to support this functionality.

 

With that said, this is the process to enable snapshot isolation on a database:

ALTER DATABASE <my_db> SET allow_snapshot_isolation ON
ALTER DATABASE
<my_db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE
<my_db> SET read_committed_snapshot ON
ALTER DATABASE
<my_db> SET MULTI_USER


To verify:
SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='
<my_db>'

Tags: Untagged

SQL Server Index Rebuild Progress

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Friday, 31 August 2012
Blog 0 Comments

Here is a query to monitor index rebuild progress:

 

;WITH cte AS
(
SELECT
object_id,
index_id,
partition_number,
rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
)
SELECT
object_name(cur.object_id) as TableName,
cur.index_id,
cur.partition_number,
PrecentDone =
CASE
WHEN pre.rows = 0 THEN 0
ELSE
((cur.rows * 100.0) / pre.rows)
END,
pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)
ORDER BY 4

Tags: Untagged

Changing dbsnmp/sysman passwords? EM needs to know...

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Wednesday, 22 August 2012
Blog 0 Comments

If you have a need to change the passwords for the sysman or dbsnmp users you will also need to edit configuration files used by Enterprise Manager. These files are $ORACLE_HOME/<hostname>.<instance_name>/sysman/config/emomes.properties and $ORACLE_HOME/<hostname>.<instance_name>/sysman/emd/targets.xml

The passwords may appear as encrypted. You can replace with clear text if you change the encrypted directives to FALSE. 

Tags: Untagged

Querying the Oracle AWR tables

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Wednesday, 11 July 2012
Blog 0 Comments

AWR snashots are a useful way to gain insight into what was going on in the database in recent history. Viewing snapshot reports can supply very detailed metrics for a specific interval (default is hourly). One limitation here is when you would like to analyze a specific metric over multiple snapshots. A way to accomplish this type analysis is to query the AWR tables directly. In my case I wanted to review session logical reads over all snapshots to get a feel for overall workload of the database over time. One thing to be aware of is that some AWR values are cumulative and so must be compared to the previous snapshot in order to see what accumulated over the interval. Using analytic functions here allows you to do this easily. See the example below and along with a link for more details on the AWR tables.

select s.END_INTERVAL_TIME, value-lag(value) over(partition by stat_name order by s.BEGIN_INTERVAL_TIME) session_logical_reads
from DBA_HIST_SNAPSHOT s

 

join dba_hist_sysstat ss on s.snap_id = ss.snap_id
where ss.stat_name like '%session logical reads%'

http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html#script-find-expensive

Tags: Untagged

CATALOG BACKUPPIECE

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Monday, 14 May 2012
Blog 0 Comments

On occasion I have had to move rman backup pieces around due to disk space shortages, etc. In order to avoid complexities if a restore is required you should re-"register" these backup pieces with rman so that it knows where they are. The CATALOG BACKUPPIECE command serves this purpose.

syntax:

CATALOG BACKUPPIECE '/path_to_backup_piece/filename1',  '/path_to_backup_piece/filename2', ...

After you are done you should follow up with a crosscheck backup command.

Tags: Untagged