SQL08: affinity_mask, IO_affinity_mask and like riding two on a single server environments without being "walked"

By il_masacratore on Mon, 04/19/2010 - 10:56

We put ourselves in position
In our environment we may need to have two replicas of a / s data base distinct environments (the classic example would be production and test). In deciding as we do the most common questions we must ask ourselves are:

- Is this new environment will be temporary? Does large data bases in terms of volume and / or load to be borne is high (even test)?
- Is there version SqlServer2008 development? That alone is at hand if you have an MSDN subscription ...
- Is there an extra server?

Based on these questions and all that it can happen one can opt for different solutions:
"The easiest and if the database to bear the burden are small we can use the same server for all databases (created on the same server with different names (_test) and Holy Easter ...). In order not to disturb each other we can use Resource Governor.
"The most" aseptic "if resources permit and where it would be worthwhile to mount on different servers (if we have the development version)
"Another option is a mixture of the above. Fit the two environments on the same server but different instances.
-Etc ...

1 server cpu n (n> 1) + 2 = 2 instances environments

Improve MySQL performance by adjusting some parameters

By Carlos on Tue, 04/20/2010 - 11:29

MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.

Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.

I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)

The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.

The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.

Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.

 

How to disable autocommit option in SQL Server Management Studio

By il_masacratore on Tue, 04/27/2010 - 11:02

Have you ever wondered how to disable the autocommit option in Sql Server Management Studio?

The answer is quick. Simply uncheck the option SET IMPLICIT_TRASLATIONS. To do it follow next path from the menu:

Tools> Options> Execution of the query> SQL Server> ANSI, then Uncheck Option SET IMPLICIT_TRANSACTIONS.

 

 

It seems rather simple but is a common doubt when you start using SQL Server Management Studio. By sharing this tip I hope to save time to someone that wants to disable the autocommit option in SQL Management Studio.

[[ad]]

SQL08: Synchronization Database Microsoft Dynamics AX 2009 on SQL Server 2008

By il_masacratore on Tue, 04/13/2010 - 11:08

For those database administrators who have to deal with such a Dynamics Ax 2009 and his henchmen (developers, consultants, etc. )  I leave here a couple of things you should know (or I should say) when we join ax2009 and sql server 2008. Sometimes you can point to the database as a source of the problem but not always. Some requirements to consider for installing Ax2009 are that the user you want access to the system should be user and DOMAIN in sql server role must be a member of securityadmin dbcreator and to create the new database from Ax installer. Once installed (or during the installation process) the problem with the database that we can find include: 

The cost of having high availability systems with Oracle

By dani on Mon, 04/26/2010 - 11:18

[quote]What's the cost of downtime to your business? $100,000 per hour, $1,000,000 or more? The recent volcanic ash that has grounded European flights is estimated to be costing the airlines $200M a day. In the IT world, High Availability (HA) architectures allow for disaster recovery as well as uninterrupted business continuity during system failure...[/quote]

https://bigdatamatters.com/bigdatamatters/2010/04/high-availability-wit…

Defragment to optimize MySQL tables

By Carlos on Mon, 04/26/2010 - 12:49

 

In MySQL, when you delete records from a table, space is reallocated automatically. It is as empty space and forming new attachments will advantage.

The problem is that if a table perform many DELETE operations, the physical space of the table will become increasingly fragmented and the performance is reduced.

In the MyISAM and InnoDB, OPTIMIZE TABLE command available to perform an optimization on any table that, among other things, performs an automatic defragmentation of the table.

It is highly recommended to use this command regularly especially on tables that are more statements of disposal of records.

As a precaution, keep in mind that during implementation, of course, the table is blocked. You have to remember when you are going to use with large tables and busy.

Supersimples The syntax is:

 

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

To make a fragmented table optimization can be selected to have free space, probably as a result of DELETE statements:

Database Express. One way to start with the big ones.

By respinosamilla on Tue, 04/13/2010 - 10:36

In a previous blog entry ( OpenSource Databases. Why did we choose for our project Mysql?), talked about the Open Source databases such as interesting and reliable option for the development of business intelligence projects. We saw some different products and compare between them.

But there are other alternatives (with limitations in most cases) that allow us to start working with "big" of a free. Express versions are called. Versions are designed for small systems, for development work or training, for prototyping or evaluation, that allow us to "initiation" with the great managers of relational databases. In most cases, the product is offered fully functional, but with limitations (the maximum size of the database, use Ram memory or processors of the machine, etc). There may also be features that are not active in these versions (such as partitioning version  of Oracle Express).  I leave the link to the manufacturers' web sites where you can download these versions:

Update SQL Server table statistics dynamically throughout a database

By il_masacratore on Thu, 03/04/2010 - 11:53

In Oracle databases there is a table that allows to list all the tables in the database (table 'dba_tables') and we can use this 'dba_tables' to create maintenance scripts dynamically.  In SQL Server we can create also scripts of tables maintenace by querying the table [dataBase].dbo.sysobjects.

In the example below we use a T-SQL script to update statistics for all tables in a SQL Server database by querying dynamically the data dictionary (using the table dbo.sysobjects). This T-SQL code can be encapsulated in a stored procedure or in a job to be executed by the SQL Server Agent to automatically keep statistics updated on all tables of the dbo scheme in a SQL Server database.

Oracle10g: Change the character set of the database

By il_masacratore on Wed, 03/10/2010 - 10:23

It may happen that after you install or configure a new Oracle database we realize that the character set chosen during installation is not correct. What we may happen in cases like this is to delete the database and reconfigure it or worse ...But you do not. We can change the character set stopping the database, looking up strictly by changing the settings and restart the database. Howto:
 

- First we connect to the database 

$ sqlplus sys/pwd@prod as sysdba
 

- We stop the database 

SQL>SHUTDOWN IMMEDIATE;

 

- We raise strictly * 

SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER DATABASE OPEN;

- Change the character map 

SQL>ALTER DATABASE CHARACTER SET <new characters map>;

- Restart the database and yata 

Oracle10g: Manual standby database (initial approach)

By il_masacratore on Wed, 03/24/2010 - 12:15

An Oracle database in Standby is an exact copy of an operational database on a remote server, used as a backup, and copy for reference, disaster recovery, etc..

A database in the Standby mode is more than a normal backup because it can be put into production disaster in less time than if we had to restore a copy (either from a simple rman or export).Restore a copy from file takes time, and during this period the system is unavailable. With an additional database in standby mode there is nothing (or almost nothing to restore) in case of disaster. Within minutes, allowing the change is continuity of service. It offers the performance advantages of a cluster or safety of the mirror but the ratio of costs versus benefits and leave time seems right to me.

From a global perspective: