New year, new post, new MongoDB

Everytime I think of MongoDB I remember Sheldon Cooper playing Bongo on TBBT tv series. Just for fun, because of the sound of the words.

But to start a new year nothing better than a new DB system. Please take a look at the announcement page for MongoDB 3.0.

https://www.mongodb.com/blog/post/announcing-mongodb-30

PS: this image did not scale well… what an irony!

cya!

Posted in Uncategorized | Leave a comment

Last post of the year: SQL Server 2014 Checklist for Performance

I would like to share these kind of things directly from SqlServerCentral, and not only posting links. But linking seems the best way to do it currently. Also I decided to increase that post count to 20 (yea, the yearly stats are post #19). Just for the fun of it :)

Be sure to read these quick tips (and discuss in the forums) because it may help you more than you can imagine. This is a greate piece by Rudy Panigas.

http://www.sqlservercentral.com/articles/Performance/119308/

I would like to add two little tips:

  • Keep your app data on a single database. Cross-database queries are bad.
  • Forget that Linked-Servers exist. Use other technology instead.

cya all next year!!!

Posted in SQLServer | Tagged , , | Leave a comment

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog. (They did not have much work, only 18 posts this year. Gotta increase that number.)

Here's an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 90,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

Click here to see the complete report.

Posted in Uncategorized | Leave a comment

SQL Server: Service Packs and Database Mirroring

Usually the Mirroring feature of SQL Server is pretty resilient to random failures at several parts of the link between the two servers. It is so resilient because it uses only built-in features that are not affected by the outside world – so you can boot your servers, cut and reconect the link, do a lot of stuff and the database will still resume mirroring once everything is good again.

But a Service Pack is something that messes with those built-in SQL features, so this can definetily mess up with your mirroring session. Please be safe and perform these kinds of upgrades gracefully. Kinda, like reading this link: http://www.mssqltips.com/sqlservertip/2701/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases/ .

Because if you fail to do so, you may end with two suspended databases. And in this case you will have to take all your servers down, upgrade them all to the same SP level, restart them all, make use of the three little commands noted below, and pray for the database god/goddess/entity to resume your mirroring session. If everything goes fine, you will see your mirroring session back to “Synchonized” state.

See, I can not guarantee that this will work. But, you know, mirroring is resilient.

USE MASTER
GO
ALTER DATABASE <your_db> SET SAFETY FULL
GO
ALTER DATABASE <your_db> SET PARTNER RESUME
GO
--optional
ALTER DATABASE <your_db> SET SAFETY OFF
GO

Edit: I though about creating a new post just to show how resilient Database Mirroring is. But instead I will just do this quick note: even if your mirror server runs out of space, Mirroring will be able to resume as soon as you add some space. You may see the event below in your mirror instance, so be sure to keep an eye open for it (this could cause your main DB Log file too grow a lot too, so you have two symptoms for this situation).

Error: 1454, Severity: 16, State: 1.
Database mirroring will be suspended. Server instance 'YOUR_SERVER' encountered error 5149, state 3, severity 16 when it was acting as a mirroring partner for database 'YOUR_BASE'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
Database mirroring is inactive for database 'YOUR_BASE'. This is an informational message only. No user action is required.
Bypassing recovery for database 'YOUR_BASE' because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.

To fix it, simply expand the data disks on your mirror server (I’m assuming you know how to do this, since you are reading this kind of article) and issue the command “ALTER DATABASE SET PARTNER RESUME”. Let the Mirroring magic happen, et voilà, database synchronized again.

cya!

Posted in Uncategorized | Leave a comment

Free Microsoft exams: get your voucher now!

I was not able to check if there is some exam for SQL Server, but this can help someone anyway.

http://vouchers.cloudapp.net/mcp/default.aspx

Quick recap of conditions:

  • Offer good until December 31, 2014 for up to a total of 10,000 vouchers distributed worldwide.
  • Eligible exams: A voucher may be redeemed to take one of the following MCP exams: 71-532 (beta), 70-533, 70-346 or 70-347. This offer does not include Microsoft Technology Associate exams.
  • Offer ends on December 31, 2014 or while supplies last. Individuals must register for and take all exams prior to December 31, 2014.

Exam 71-532 Developing Microsoft Azure Solutions (beta) – This exam is currently in beta.
Exam 70-533 Implementing Microsoft Azure Infrastructure Solutions
Exam 70-346 Managing Office 365 Identities and Requirements
Exam 70-347 Enabling Office 365 Services

Cya!

Posted in Uncategorized | Leave a comment

Talk about MS-SQL, Oracle, NoSQL and Babylonian accounting tablets

Looking at the past can teach us a lot of things. This is a great way to know more about the things you like. And this is a great article about some fundamental characteristics of the major database systems today.

It’s definitely worth a look.

https://www.simple-talk.com/opinion/opinion-pieces/ninja-immutable-databases/?utm_source=ssc&utm_medium=publink&utm_content=ninjaimmuntable

Fun quote:

You can either have data under transactional control, or you can have anarchy; take your pick. — Robert Young

cya!

Posted in MongoDB, MySQL, Oracle, SQLServer | Tagged , , | Leave a comment

Oracle: Error ORA-00955 name is already used by an existing object

Sometimes things just won’t work as expected. You know, programming has its quirks and the like. Normally this error alone would mean you are creating some object with a name that is already in use. But what if you just deleted the previous object and get this error when you try to recreate it?

ERROR at line 1:
ORA-00955: name is already used by an existing object

ORA-00955 in SQL Developer

ORA-00955 in SQL Developer

In Oracle, this if possible if you are deleting a unique constraint. This happens because a Unique Constraint is also an Index. Consider you just deleted the old constraint and now tries to create the updated constraint with the same name. Bang:  ORA-00955.

To fix it you must also delete the index that has the same name as the Unique Constraint. Then you will be able to create the constraint again, that will recreate the index too (with the same name – so much for the “name in use” rule, right?). BTW, the same happens with Primary Keys.

Actually, this is the kind of thing that makes life an adventure. Not always pleasant, but an adventure none the less. Below are some code snippets for your delight. Remember: Oracle is case-sensitive for text comparisons.

--Drop Unique Constraint
alter table mytable drop constraint UC_mytable;
--Drop Index for Unique Constraint
drop index myschema.UC_mytable;
--List all indexes from a table
select * from all_indexes where table_name = 'MYTABLE';
--List all constraints with a specific name
select * from all_constraints where constraint_name = 'UC_mytable';

And now some links for future reference also:
List of some ORA errors: http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90202/e900.htm

Some folks with the same problem: https://community.oracle.com/message/9907669 and http://stepintooracledba.blogspot.com.br/2013/03/ora-00955-name-is-already-used-by.html

That’s it for today. Cya!

Posted in Oracle | Tagged , , , , , | Leave a comment

Enable / Disable Users (not Logins) in SQL Server

mauriciorpp:

Revoke connect is not exactly Disable, but the icons are similar. Too much similar. Read on.

Originally posted on Jim Humphries:

A DBA friend of mine came across a strange problem the other day. He discovered that one of the users in a SQL server database was disabled, thats a database user not a server login. At least we thought it was disabled at first, it behaved as if it was disabled and it had the small red down arrow icon that disabled server logins have, but as we all know there is no way to enable or disable database users in the Management Studio GUI.

I had a quick Google and was quite surprised to discover that lots of people had the same ‘issue’ but most peoples workaround was to delete the user and re-create it.

After some more research, it turns out that the user didnt have CONNECT permissions to the database (which I guess amounts to the same as being disabled). You can grant CONNECT permissions using…

View original 23 more words

Posted in Uncategorized | Leave a comment

MS SQL: Find total server disk space in T-SQL

Hello. Quick post today. I needed to find the total disk space for a remote server with access only to T-SQL. No PowerShell or file system sneak-peek was allowed. Actually I would send my queries to someone else run it, and only then I could look onto the results.

So starting of this post from Pinal Dave and this MSDN article I came up with a quick query. It’s not exactly elegant, but gets the job done. SQL 2008 R2+ code ahead.

SELECT distinct(volume_mount_point), 
  total_bytes/1048576 as Size_in_MB, 
  available_bytes/1048576 as Free_in_MB
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 order by 1

This solution was also posted on SQLServerCentral forums. That is a great place to search for such things.

Also get some familiarity with sys.dm_os_sys_info and EXEC xp_msver; - those can be of great help in such dire situations.

cya!

Posted in Uncategorized | Leave a comment

Virtualizing your databases guide

Recently the guys at SQLServerCentral published a great article about SQL Server virtualization. But it is usefull for SQL Server, Oracle or any other DB technology that must run “on the cloud”.

So here is the link to it: http://www.sqlservercentral.com/articles/Stairway+Series/112555/

And that’s it. Go read it over there, but come back here after.

cya!

Posted in Cloud, MongoDB, MySQL, Oracle, SQLServer, VMware | Tagged , , | Leave a comment
Follow

Get every new post delivered to your Inbox.

Join 47 other followers