December 8, 2009
Sybase ASE 15: Very Large Storage Systems
by Jeffrey Garbus
Executive Summary:
In this past year, the author has had multiple customers accelerate their migrations to ASE 15 with one goal in mind: taking advantage of Very Large Storage Structures (VLSS) in ASE 15 so that they can increase the size of their growing databases to something with an almost unlimited upside. The idea behind VLSS is that Sybase has modified the ASE system tables to accommodate the larger limitations. In this article old structure, new structure, and practical limitations are discussed.
Introduction
From its inception, Adaptive Server Enterprise (ASE) has been a high-volume Online Transaction Processing (OLTP) or Decision Support System (DSS) or mixed-use database, capable of storing, managing, and retrieving an enormous amount of data.
Over time, the definition of the word “enormous” has changed.
In the early 1990s, we designed and rolled out an application, which contained about 65 gigabytes of data. At the time, our contemporaries wondered if the system would even accommodate a table with 2.5 billon rows of data in a single table (it did). It was, at the time, the 4th largest ASE installation in the world. Anything over 20 gig was considered a Very Large Database (VLDB). Within a few months it had doubled in size, and a few months later doubled again to 250 gig, a monster in those days. The entire project was cost-justified in storage savings: a 2-gigabyte DASD device (for their DB2 applications) cost approximately $40K, while a 2 gigabyte SCSI drive cost about $2K.
Today, you can buy an external 1Terabyte hard drive for under $100. Times change.
In this past year, I have had multiple customers accelerate their migrations to ASE 15 with one goal in mind: taking advantage of Very Large Storage Structures (VLSS) in ASE 15 so that they can increase the size of their growing, 4 terabyte databases to something with an almost unlimited upside. (Yes, famous last words, but with a potential upside of 2 billion devices of 4 terabytes each, I’ll risk eating those words).
The idea behind VLSS is that Sybase has modified the ASE system tables to accommodate the larger limitations.
In this article, we’re going to discuss old structure, new structure, and practical limitations.
Pre-ASE 15 Storage
Prior to ASE 15.0, ASE was limited to 256 logical devices and 32GB for individual devices. Note that when these limits were originally put in place, 20 gig was thought to be a Very Large Database (VLDB), and it was years after before anyone attempted one. In addition, because of an intra-database addressing limitation, an individual database was limited to 4T (if you have a 2K page size) and 8T (if your page size is larger). These limitations were irrespective of physical device or OS-level limitations.
This was driven by the fact that ASE’s storage was organized by virtual pages using a 4 byte bit mask. The virtual page was the mechanism the server used to connect the logical concept of a page with the physical disk. The high order byte of the vdevno column in the sysdevices table was used to encode the device id, and consequently the byte of 8 bits limited ASE to 256 devices (8 bits as a signed integer). (Of course, with one device set aside for master, and perhaps others set aside for tempdb, sysaudits, etc., there were sometimes fewer than 256 available for data). The remaining 3 bytes were used to track the actual virtual page numbers – which considering a 2K page (the storage factor in sysdevices) provided a limit of 32GB per device. Note that theoretically, a 16K server could have large devices, but due the default of 2K and the implementation of sysdevices, a limit of 32GB was imposed.
---------------------------------------------------------------------
December 4, 2009
MS SQL
SQL Azure with ASP Dot Net
Introduction
SQL Azure (SSDS - SQL Server Data Services) is a cloud database system offered by Microsoft. We interact with the SQL Azure services by either issuing statements to it though a command prompt or developing Dot Net applications. This article will introduce and demonstrate development of SQL Azure ASP Dot Net applications.
Connecting
Connecting to SQL Azure is similar to connecting to a traditional SQL Server. A connection string is created then opened. To begin with SQL Azure, create an account from the Microsoft Azure web site. Once the account is created, log in and navigate to the Server Administration page. Select the master database (master is automatically created for us when the Azure account is created), and then select “Connection Strings”. A popup will display the connection string for ADO Dot Net and ODBC. Copy the ADO Dot Net string. On the next tab, Firewall Settings, create a rule to allow your client machine access as shown below.
The IP address will be your outside connection address. The firewall tool will display the IP address you connected from. SQL Azure services are firewall protected, so each client requiring access will need a rule. If you're developing from a machine which receives a DHCP address from an internet service provider, then a new rule will need to be created each time a new address is received (unless you know the IP address range, then it can be specified here). Allow up to five minutes for the new firewall rule to go into effect.
Create a new Dot Net web site and aspx page from Visual Studio. In the Page Load section, add using statement s for System.Data and System.Data.SQLClient just as we would for a traditional SQL Server web page. Save the connection string as a variable and pass it to the SqlConnection object as shown below:
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;
Database=master;
User ID=dons;
Password=mypassword;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
objConn.Close();
}
The connection string ID and password were specified when the Azure account was first created. The server section specifies the fully qualified name. In this example, qdpapn8, is my personal unique server that SQL Azure auto generated for my account. My personal server name is appended to the public “database.windows.net” domain to create the Fully Qualified Name (FQN). Note the “tcp” prefix to the server name. SQL Azure listens on TCP port 1433, so our client workstation must allow outbound traffic on this port. The Trusted Connection will always be set to False, as Windows Logins are not supported on SQL Azure. Executing this web page should result in a blank Internet Explorer window. If an error is thrown, it will be displayed there instead.
CREATE DATABASE
Now that we have a valid connection string, we’ll create a test database. The TSQL syntax for SQL Azure is usually identical to traditional SQL Server. A full list of supported statements, limitations, and options is available from this Microsoft web site: Transact SQL Reference SQL Azure Database. In this example, we’ll pass a CREATE DATABASE statement to a SqlCommand object as shown below. Remember to add the System.Data and System.Data.SQLClient using statements to all examples in this article.
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;Database=master;User ID=dons;
Password=mypwd;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
string sSQL = "CREATE DATABASE TestDB";
SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
objCmd.ExecuteNonQuery();
objConn.Close();
}
The CREATE DATABASE statement was saved as a string and then passed as a Text Command statement. After executing this page, a new 1 GB database was created as shown in the Server Administration web page.
Currently, there are two different database sizes, 1 GB or 10 GB, each with a different pricing policy.
CREATE TABLE
To create a Table in the new database, change the connection string specifying the new Database Name and change the statement to a typical CREATE TABLE as shown below. This example will create a two-column table.
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypwd;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
string sSQL = "CREATE TABLE myTestTable
( FirstName varchar(50) NOT NULL, LastName varchar(50) NULL,
CONSTRAINT PK_TestTable PRIMARY KEY (FirstName) )";
SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
objCmd.ExecuteNonQuery();
objConn.Close();
}
Note that the CREATE TABLE statement included a key, in this case a PRIMARY KEY. Heap tables (tables without an index) are not allowed in SQL Azure.
INSERT
The syntax for inserting data into SQL Azure is identical to traditional SQL Server. Here we’ve modified the SQL string with an INSERT. The rest of the code, SQLConnection, SQLCommand, etc., are identical to the previous examples.
string sSQL = "INSERT INTO myTestTable (FirstName, LastName) VALUES ('bob', 'smith') "; SELECT
To receive the newly inserted row back, drag a GridView control onto a new page and modify the SQL string to a SELECT statement. Then we’ll bind the GridView to the SQLCommand object and execute the Reader as shown below:
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypwd;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
string sSQL = "SELECT * FROM myTestTable";
SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
myGridView.DataSource = objCmd.ExecuteReader();
myGridView.DataBind();
objConn.Close();
}
The result will be our one inserted row being returned as shown below:
Stored Procedures
In my opinion, one of the best features of SQL Azure is the ability to create Stored Procedures in the cloud. This is a rare feature, most cloud databases do not support stored procedures and instead database code must be passed as a string. By using Stored Procedures, we create a clear separation between database code and application code. In addition, it’s easy to share the same stored procedure between many web pages and at the same time we avoid SQL Injection attacks. The syntax for creating a Stored Procedure in the cloud is the same as traditional SQL Server. In this example, we’ll simply modify the SQL string as shown below:
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypassword;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
string sSQL = "CREATE PROCEDURE GetNames AS SELECT * FROM myTestTable";
SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
objCmd.ExecuteNonQuery();
objConn.Close();
}
To execute the Stored Procedure, we’ll drag a GridView onto the form; bind that grid to the SqlCommand object, and then call the ExecuteReader as shown below.
protected void Page_Load(object sender, EventArgs e)
{
string sConn = "Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypassword;Trusted_Connection=False";
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
SqlCommand objCmd = new SqlCommand("dbo.GetNames", objConn);
objCmd.CommandType = System.Data.CommandType.StoredProcedure;
myGridView.DataSource = objCmd.ExecuteReader();
myGridView.DataBind();
objConn.Close();
}
Conclusion
Working with SQL Azure (SQL Server Data Services SSDS) leverages our existing TSQL and Dot Net skills. The TSQL for the cloud usually has the same syntax as a traditional SQL Server, and calling the cloud from a web page uses the same commands and readers as other familiar data sources. Having the ability to create relationships, use TSQL, and execute stored procedures in the cloud are strong benefits of SQL Azure.
