I am my own brand [Un-SQL]

Posted on by
1

I am done with all my meetings, prepared for tomorrows migration and deleted all the fluff from my inbox. Now I have time to add a post for the Un-SQL Friday, an idea by the @MidnightDBA, yes the one that is rough to vendors that do not know anything about databases and DBAs wanna-be that are just incompetents (won’t argue with that). The original post is located here.

Anyways, I am my own brand. Does it matter? Maybe. That is why I created ozamora.com. That is my brand. But, do I want to be known? Not necessarily want but would like to be seen as someone that has helped building solutions or solved a problem, because that is pretty much what I do on a daily basis. I also like sharing what I have learned from others.

DJ Zamo

Now I wish I had a chance to post most interesting stuff I do so I can go back to it and say “oh, this is how I did it” instead of trying to remember. It has happened to me several times and no, searching emails not always answers my questions.

But why ozamora? Just to keep it simple. I have used ZamoTrance, Zamo, OZ, DJ Zamo, and even some have called me Zamorita, Zamito, and the Wizard of OZ. I have not registered them yet, as I am waiting for a GoDaddy Deal ha!

Now Twitter. Yes I am late to the game and started with @Oscar_Zamora, then changed to @__ZT and finally settled with @ZamoraO? Why not @ozamora? Well you guessed it. There is someone that has registered it a long time ago and never has tweeted. Twitter needs to phase out all the stale usernames…

I did register zamorao.com, so I guess I am covering my brand.

Has it helped? Certainly. I met many people at the SQL Pass Summit this year, and they recognized me just because of my Twitter name.

I will continue with ozamora.com and pack it with information that interests me and might be useful to the community. My goal is to excel at what I do and transfer knowledge to my team. If I do not learn something new today, I feel like I just wasted it. I make sure it does not happen.

That’s it. Now follow me.

Denali, the next release [SQL Server]

Posted on by
Reply

After a week at the Pass Summit United 2010 and after catching up at work with several tasks, I am now able to update my blog with some exciting information. SQL Server codename “Denali” CTP1 was released formally on 11/09/2010 (version 11.0). I was able to obtain a DVD copy while at the summit, and now it can be downloaded too.

The key takeaways:

  1. AlwaysON. This is a full blown HADR solution that allows multiple asynchronous and synchronous
    SQL Server Codename "Denali" Community Technology Preview 1

    SQL Server "Denali" CTP1

    mirrors of a database with transparent fail over option to a local and remote datacenter. This is a feature I am really looking forward to.

  2. VeritPaq embedded in the relational engine. The SQL Server engineers ported the same code used for PowerPivot into the RDBMS. The feature will allow column-level ultra-high compression of repetitive data which will dramatically increase query efficiency by ten times at least.
  3. BISM with DAX support. Business Intelligence Semantic Model in Analysis Services which will allow retrieval of data using DAX queries; the same ones used in PowerPivot.
  4. Sequencer generator. It is never too late for a feature like this.
  5. Enhanced Windowed Functions.
  6. File Table Support. I have been waiting this feature since the Oracle 9i days. It will allow us to manage files directly within SQL Server.

There are certainly more enhancements which will continue to pop-up as the product matures.

Now, it is a challenge for us to learn and prepare for the new features after just upgrading to 2008 R2. Well, Microsoft is doing a pretty good job by keeping us busy and I am OK with that. They were kindly enough to publish Books Online for Denali though.

Split a Partition and Expect Locks [SQL Server]

Posted on by
1

Not in every scenario. But as a rule of thumb, test before you perform a split operation in a production environment.

Every year around this time we plan, test and deploy partitions for tables that will hold historical data from the next year. We typically create one partition per month and in one case a partition per day (which has its own sliding window schedule every month).

This has been a very busy year, and between the migrations and consolidations a historical table did not have partitions added for October and November. I took the decision to keep the data as-is and just add a new partitions starting on 11/12; one for each day. This table holds around 30 million rows per day.

Now, we usually partition tables using RIGHT functions in order to have the last boundary empty and able to split it beforehand without any locking. The table I mention in the above paragraph is using a LEFT partition function and all the current data is sitting in the rightmost partition.

Issuing a Split partition of the rightmost data will let the engine scan and move the data for the current partition. This is something that can be fine in systems with a few million rows, but not with a partition holding 500+ million rows. We tried on a testing environment and as expected it locked the table for a very long time. DML operations were not able to be performed against it until the split succeeded.

1
2
3
-- Using new filegroup
ALTER PARTITION SCHEME [ps_Left_day_sliding] NEXT USED [OEN_Lists_2010_11];
ALTER PARTITION FUNCTION [pf_Left_day_sliding] () SPLIT RANGE ('2010-11-12 00:00:00.000');

This is the table which has a partition per day.

We are not going to purse that route. We will create a brand new table with proper RIGHT partitions and swap it with the current one. We have that liberty as the data is purely historical, and DBAs retrieve from it when required. They will know where to look at after reading at the documentation they write after performing such a change.

We went ahead and also tested with RIGHT just to double make sure. It worked just fine.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE PARTITION FUNCTION [pf_Left_day_sliding1](datetime) AS RANGE RIGHT FOR VALUES (N'2010-09-03T00:00:00.000', N'2010-09-04T00:00:00.000', N'2010-09-05T00:00:00.000', N'2010-11-12T00:00:00.000');

CREATE PARTITION SCHEME [ps_Left_day_sliding1] AS PARTITION [pf_Left_day_sliding1] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [OEN_Lists_2010_11]) ;
CREATE TABLE [dbo].[batchresults_buffer1](
[column1] [bigint] NOT NULL,
[column2] [datetime] NOT NULL,
[column3] [int] NOT NULL,
[column4] [int] NOT NULL,
[column5] [int] NOT NULL,
[column6] [varchar](200) NOT NULL,
) ON [ps_Left_day_sliding1]([column2]);
CREATE UNIQUE CLUSTERED INDEX [PK__batchresults_buf__2077C861] ON [dbo].[batchresults_buffer1]
(
[column1] ASC,
[column2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_Left_day_sliding1]([datecreated]) ;
BEGIN TRAN;
INSERT INTO [batchresults_buffer1] (column1, column2, column3, column4, column5, column6)
SELECT
column1, column2, column3, column4, column5, column6
FROM batchresults_buffer; -- This table contains sample data, 50 million rows.
COMMIT;
ALTER PARTITION SCHEME [ps_Left_day_sliding1] NEXT USED [OEN_Lists_2010_11];
ALTER PARTITION FUNCTION [pf_Left_day_sliding1] () SPLIT RANGE ('2010-11-12 00:00:00.000');

I do not see that need for us to use LEFT. All future deployment will use RIGHT instead.

Wi-Fi Hotspot [HTC G2]

Posted on by
Reply

After sending the OTA upgrades, TMobile enabled 3 nice features: WiFi Calling, USB Tethering and WiFi HotSpot. The HTC G2 can be used as a wireless router and be able to accept connections from any device that connects to Open and WPA2-PSK Wi-Fi networks.

Portable WiFi Hotspot

Portable WiFi Hotspot

Portable WiFi Hotspot Configuration

Portable WiFi Hotspot Configuration

Just follow the steps:

  1. On the Android phone, press Home, press Menu, and touch Settings to open the Settings application.
  2. Touch Wireless & networks > Tethering & portable hotspot.
  3. Check Portable Wi-Fi hotspot.
  4. Check Portable Wi-Fi hotspot Settings
  5. Configure the Netword SSID, Security and Save
  6. Your phone is now a Wireless Router using the Available HSPA+ network.

It seems that T-Mobile isn’t charging extra for this feature yet, but will throttle if bandwidth usage goes over 5 GB within a month.

USB Tethering [HTC G2]

Posted on by
1

After ensuring that WiFi calling worked properly I went ahead and researched about USB tethering for my G2. I have tried PDA Net before, but I wanted to use a “native” solution provided by T-Mobile. It is really easy.

USB Tethering

  1. You are likely to need drivers, so download it from here and decompress into a folder.
  2. Use the USB cable that came with your phone to connect your phone to your computer.
  3. On the Android phone, press Home, press Menu, and touch Settings to open the Settings application.
  4. Touch Wireless & networks > Tethering & portable hotspot.
  5. Check USB tethering.
  6. When asked for drivers, point the the one you just downloaded.
  7. You should be able to connect to the internet at this point.

If your phone is connected to the Internet through WiFi, the tethered computer will use your phone as a gateway, using wireless internet instead of the available HSPA+ network. This is good for computers that do not have a wireless connection and not able to connect to the internet.

Speed Tests:

Tethering using 3G Network (No HSPA+ available)

Tethering using 3G Network (No HSPA+ available)

Tethering using Wi-Fi and local Internet Service Provider

Tethering using Wi-Fi and local Internet Service Provider