Find Communities by: Category | Product

These two events happened in relatively quick succession.  I started getting questions about positioning because at first glance, it looks like they might be contradictory.  In fact, they solve two, very different  customer challenges.

 

Twinstrata is all about leveraging the cloud for storage, and keeping your compute local.

ExpressRoute is all about leveraging the cloud for compute, while keeping control of your storage.

 

So, if your customer is looking at cloud to provide fourth (or fifth) storage tier, Twinstrata fits the bill. 

 

If your customer is looking at cloud to augment (or replace) their compute, but don't want to (or can't) leverage cloud for their storage, then ExpressRoute is part of the answer (at least for Azure).  I believe this has a wider variety of use cases, as there are more numerous obstacles to cloud storage than there are to cloud compute.  These range from data availability SLAs, data governance issues, regulatory pressure and so forth.

 

We should also remember that there are uses for low-latency, high bandwidth links into public cloud provider data centers.  And it's worth noting that Amazon and VCHS have similar offerings - both uninspiringly named "Direct Connect".  I guess Microsoft had a little extra left in the tank to name their offering.

I had the chance to spend a couple of hours with Paul Galjan (@PaulGaljan) last fall while in Seattle for Microsoft’s Tech Ready.  We got on the topic of what makes effective technology demonstrations and eventually started discussing ways to generate SQL Server workloads.  I mentioned that I often time use Windows Performance Monitor to log Windows perfmon counters to a SQL Server database.  Paul thought I should share this idea with a blog post on the Everything Microsoft site.  This morning an email thread popped inside EMC on exactly the same topic.  I figured that was a sign to get busy and write this up to find out if anyone else finds this useful.

 

Software and hardware testing is a much more interesting field of work than many people might realize.   A large part of my job at EMC is to show customers how Microsoft applications work with EMC hardware and software.  I spend a lot time talking about SQL Server performance, reliability and recovery.   When I need to show something like hardware based remote replication with RecoverPoint it really helps to have a live database under load during a demonstration.  Most of what I do is feature and functionality testing that doesn’t require a workload large enough to test the limits of large hardware configurations and/or enterprise software.  For these situations let me show you how I use Windows Performance Monitor.

 

The first step is to setup a database on the server that you want to use as the load target, for example, the primary replica of an Always On Availability Group.  Use SQL Server Management Studio (SSMS) or a script file to generate an empty test database.  Make sure that you pay attention to the initial data and log file size settings.  If you want to show what happens with small files and autogrow enabled you can keep the default data and log file settings.  If you don’t want to be impacted but autogrow operations create files sized appropriately.  I usually size my files to be about 10% smaller than the physical disk size set maximum files sizes equal to 95% of the disk size.  That way if I fail to notice that the disk is running out of space, SQL will shut down before Windows freaks out.  I have used this type of setup to show how thin LUN expansion on VNX works.  It is very effective because most DBAs have seen this happen.

 

Here is a look at the New Database dialog from SSMS showing the default (ridiculously small) file sizes.  I’m going to use the defaults for creating the screen shots I need for this article. You will want to change the initial database size and autogrowth settings to match the scale to which you think this will grow.

Number 1.png

The next step is to go to Administrative Tools | ODBC Data Sources on the computer that you are going to use for generating load.  This can be either the same machine that is running SQL Server or a remote machine.  If you are using a remote machine make sure that Windows Firewall on both machines allows the remote SQL connection on the TCP/IP port that SQL Server uses or is that the firewall is turned off if this is a secure and dedicated testing network.  In this example I’m configuring the connection on a single server that will be both the load generator and the SQL Server system under test (SUT).

Number 2.png

 

I’m going to go through all the important screens needed to create a Data Source Name but I did want to include a shot of the first couple that shows I’m using a System DSN and selecting the SQL Server driver.

Number 3.png

Make sure you change the default database for the ODBC connection to the blank database that you created above.  Otherwise you will end up creating new some new tables and logging your Perfmon data to the MASTER database.  Not to terrible in a test environment but it might take you a while to figure out where the data is going because everything from here on out will still work as long as the user that is authenticating to SQL Server has rights to MASTER.

Number 4.png

I always use the Test Connection option before completing the New DSN wizard to insure there are no authentication, network or firewall issues.

Number 5.png

Now that we have a DSN for the database that is going to be the destination of our perfmon data everything else happens with Performance Monitor so let’s take a look at how that gets setup.  The first thing you’ll need to do is create a new user defined data collector set.  I always start with the System Performance template.

Number 6.png

I creatively called my Data Collector Set "Performance Data".  You now need to change the location and type of logging destination.

Number 7.png

Drill into the data collector set until you get to the performance counter collector that was created when you choose a template above.  Right click to get the Properties dialog.  For log format choose SQL and for Data source name choose the DSN you created above.  You can also add and remove counters from the default set and change the sample interval both of which will impact that amount of data you write to the target database.

Number 8.png

Save those settings and then open the properties for the Data Collector Set (Performance Data as shown above).  The most important setting is the Run As: account.  If you want to write to a SQL server instance across the network you need to have an account with that privilege.  I’m using a domain account with sysadmin privileges on the target SQL server.  Any time you change properties of the data collector you will need to supply the Run as credentials again.  You will also want to check out the Schedule and Stop Condition tabs to make sure your collector runs for an appropriate amount of time.

Number 9.png

Now you can right click on the Data Collector Set and choose Start or wait for the scheduled time for starting that you configured above.  Go to SQL Management Studio and look at the list of user tables for the target database.  You should see three tables that were created the first time you started the data collector as shown below.

Number 10.png

 

 

Right click on the database name and select Reports | Standard Reports | Disk Usage by Top Tables.

Number 11.png

That’s it you are logging perfmon to a SQL database.  Now you can play with Always On, Replicaiton, Backup and Restore, Log Shipping, etc with a live database and it only took about 15-20 minutes to setup the whole thing.  Good luck and have fun with this.

Every once in a while a seemingly simple question pops up on an email distribution list or Yammer thread that ends up spawning a lively and productive conversation.  We had one recently that started out with the question:

 

For SQL Server running on XtremIO, is there any current guidance we should be giving our customers to best take advantage of the 4K element size on XtremIO?

 

One of the thoughts behind the question was motivated by the existence of a 4k disk allocation unit (DAU) option when formatting a disk with NTFS.  Since a 4K allocation size significantly increases the size of the allocation structures managed by NTFS and the frequency with which allocations are made there better be a good reason for using them.  With the relatively large files sizes typical of most SQL Server applications, we aren't worried about wasted space even when using the largest available allocation size of 64K so there is really no good reason to use anything but 64K allocation units for SQL Server.  This just opened the flood gates to many more SQL I/O questions that went beyond NTFS allocation unit size and XtremIO.  I gathered together a summary of the question and best responses in the table below.

 

 

Are SQL Server write sizes independent of DAU size?


  1. Yes.

How much does the additional overhead of 4K DAU impact
  performance? Will SQL Server still maintain its write sizes?


Most of the impact of
  DAU is felt as you create new space in a data or log file.  If you let
  SQL zero initialize the data file then all the overhead for allocation occurs
  at once.  Log files always initialize upon creation.  If you use
  instant file initialization with a data file then you get a small added
  overhead every time Windows allocates new space to the file.  When I
  have tested file creation with a new data file with 4K vs 64K DAU the
  difference in time to create is only a couple of percent difference.  It
  is almost the same thing if you run 8K or 64K random reads and writes with
  SQLIO against a disk with 4K vs 64K allocation units, a couple of percent
  difference but always favors the 64K DAU.


Does SQL typically write to the log in 4KB and DBs in
  32-64KB I/Os?


Log writes can be a
  small as 512 bytes (one sector) up to 60K (the maximum SQL will allow before
  forcing a flush to disk).  See the Kendra Little article referenced
  below for lots more details about this topic.
  DB writes can be 8K (one page) up to 256K (the maximum size the gather
  write operation will bundle before flushing to disk).


SQL Server uses a lazy writer and will try to write whole
  extents (if possible)


The lazy writer is
  constantly scanning the buffer pool for pages that are not being referenced
  often.  When the lazy writer flushes a page, the buffer manager will try
  to do a gather write exactly like if the page is being written by a check
  point so that can be 8-256K.


If an extent in RAM is only partially dirty (various pages
  dirty) then will only those dirty pages get written.  That would result in a smaller write
  size.


The gather write
  operation is looking for adjacent pages from a single data file up to 32
  pages.  An extent is 8 pages. There is no consideration for “wanting to”
  or “having to” to write entire extents from the pool.  The buffer manager
  will always read an entire extent from disk even if only one page from that
  extent is required for the operation.


Typically SQL write performance is governed by TLOG write
  speed and not by DB write speed, unless doing a bulk input.


You could have a nice
  wide RAID10 set for the logs with blistering sequential write speed and a
  RAID 6 SATA set for the data file.  It is hard to make general
  statements like this but if all you are doing is writing data (no reads) then
  often times the log file ideal of 5ms per write will get exceeded before the
  20ms per write to the data file(s) that usually indicates that I/O wait time
  are going to start impacting performance.

 

Another great outcome from this discussion was the sharing of favorite white papers and blog posts.  I have collected those links and added them here.

 

SQL Server Best Practices 2012 http://msdn.microsoft.com/en-us/sqlserver/bb671430 and,

 

SQL Server Best Practices Article in 2007 http://technet.microsoft.com/en-us/library/cc966412.aspx

 

There is also an interested blog here from Kendra Little:

 

http://www.brentozar.com/archive/2012/05/how-big-your-log-writes-spying-on-sql-server-transaction-log/ …read the
comments also

 

The best IO size information I’ve seen came for the SQL CAT team a few years back.  I think it’s still relatively accurate, check out the appendix of the presentation from the link below. 

 

http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Components-PostAttachments/00-09-45-27-65/Mike_5F00_Ruthruff_5F00_SQLServer_5F00_on_5F00_SAN_5F00_SQLCAT.zip

 

For the 10 years I worked at Microsoft I found the SQL Server technical community a constant source of knowledge and challenge that made me a better SQL Server professional.  The community at EMC is certainly smaller but no less dedicated to sharing information and promoting technical excellence.  I hear the same thing from anyone involved with PASS and local users groups.  Let us know how you participate in a SQL Server community and what it means to you.  Thanks for reading.

I am excited to share with you the summary of the key New Product Features included in this release:

 

  • Integrated Microsoft Active Directory role based access (RBAC) to the ESI GUI
  • Automate disk provisioning for Microsoft SQL Server via ESI GUI
  • Set up and manage application protection using RecoverPoint and/or Appsync via ESI GUI.
  • Provision storage for Linux servers

 

Product Overview

 

EMC Storage Integrator (ESI) for Windows Suite is a set of tools for Microsoft Windows and Microsoft applications administrators. ESI for Windows provides the ability to view, provision, and manage block and file storage for Microsoft Windows, Exchange, and SharePoint sites. ESI supports the EMC Symmetrix® VMAX® series, EMC VNX® series, EMC VNXe® series, and EMC CLARiiON® CX® fourth generation (CX4) series of storage systems. ESI requires that you install the applicable adapter for your specific storage systems.

For further details on the ESI Suite and all of its components click here

 

Detailed feature set included in the 3.5 release:

 

Role based access control in ESI with Active Directory

Automate application disk provisioning for SQL Server

SCOM VPLEX health monitoring

Automate application protection with AppSync

Manage RP Bookmarks from ESI GUI

Storage provisioning for Linux host

Compatibility and Interoperability

 

 

This product is compatible with:

Windows 2008

Windows 2008 R2

Windows Server 2012

Windows Server 2012 R2

Linux (RHEL 6.x, SUSE 11, Ubuntu 12.10)

SMB 3.0

System Center Operations Manager 2007/2007 R2 / 2012 / 2012 SP1

System Center 2012 R2

System Center Virtual Machine Manager(SCVMM) 2012 SP1 UR4 or later and SCVMM 2012 R2

Exchange 2010/2013

SharePoint 2013 (SharePoint 2010 support will be dropped from 3.5)

Microsoft SQL Server 2008 R2 on Windows Server 2008 R2 and Windows Server 2012 and 2012 R2

Microsoft SQL Server 2012 and 2012 SP1 on Windows Server 2012 and 2012 R2

Microsoft SQL Server 2008 is not supported; Windows Server 2008 is not supported for SQL Server scenarios

 

VMWare vSphere 5.5 

Exchange 2013 with SP1 on Windows 2012 R2

 

 

Target platforms supported by this product are:

VNX

 

VNX2

VG2

VMAX

DMX

RecoverPoint 4.0 SP2 and 4.1

VNXe 3100, 3150, 3200 and 3300

VPLEX GeoSynchrony 5.2, 5.3 (SCOM only)

AppSync 1.6

 

Click here to download a copy of ESI 3.5

Filter Blog

By date:
By tag: