Microsoft® SQL Server® 2008 R2 Best Practices Analyzer

To verify the SQL Server setup compliance with Microsoft Best Practices has become more easier now.

Microsoft has provided “SQL Server 2008 R2 Best Practices Analyzer” tool.

One can install following two components on Server or any remote machine (additional settings will required in 2nd case)

1) Microsoft® SQL Server® 2008 R2 Best Practices Analyzer

2) Microsoft Baseline Configuration Analyzer 2.0

On Execution tool will detail out the compliance report as per the Microsoft’s best recommended practices. To tune any SQL Server we can use this as startup point…

Comments

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches: Drop Schema with cascade all and re-create it again. In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so, Create function which would do the task and then drop that function too. --- CREATE OR REPLACE FUNCTION drop_DB_objects() RETURNS VOID AS $$ DECLARE rd_object RECORD; v_idx_statement VARCHAR(500); BEGIN ---1. Dropping all stored functions RAISE NOTICE '%', 'Dropping all stored functions...'; FOR rd_object IN ( SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as functionDef FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid) WHERE ns.nspname = current_schema AND p.proname <...

My Other Post >>

Distributed transaction in Oracle ( Over Oracle DBLink)

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions: Security issue: - We cannot create Public synonym for the remote object accessed over Private DBLink of other Database. - It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema. If you try to provide the grants to other schema Oracle raises an error: [ORA-02021: DDL operations are not allowed on a remote database] “In an all you can access remote objects over private DBLink in the same schema where DBLink is created”. Fetching the Ref Cursor at Remote site: Let’s say we have two site...

My Other Post >>

Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script : -------------------------------------------------------------------------------------------------------------------------- #!/bin/sh rm -rf /tmp/vacuum_analyze_MyData.sql dbname="mydata" username="mydatadatamart" namespace="mydatadatamart" # Vacuum only those tables which are fragmented over 30% # Analyze tables only if they are not already # Process CMF and Groups Master table every time as they are master Tables=`psql $dbname $username << EOF SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList FROM ( SELECT *, n_dead_tup > av_threshold AS "av_needed", CASE WHEN reltuples > 0 THEN round(100.0 * n_dead_tup / (reltuples)) ELSE 0 END AS pct_dead FROM (SELECT C.relname AS TableName, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, pg_s...

My Other Post >>

Santosh Kangane

Search This Blog