Shared posts

14 Dec 06:55

Social Media Housecleaning

by tracy

superjanitors

Bear with me folks, this might be hard to follow. To go along with my shiny new web site, I’ve also created (or revived) some social media accounts. If you already follow me on Twitter, Facebook, or LinkedIn, you know that I’m a prolific sharer of “interesting” things. I also, from time to time, share content related to my hobbies, photography, model railroading, Fitbit stats, etc.. The point that I’m driving at is that not everything I share is SQL Server related, which makes it a nuisance to some of you who follow me strictly for SQL content. I’m fixing that today.

If you follow me on Twitter, sorry, you’re going to see everything I post – one Twitter account is enough.

If you ONLY want to see my SQL Server posts, follow me here:

If you want to see EVERYTHING I post, I’m tempted to ask “What’s wrong with you?”. Follow me here:

The full list of social media options for following me:

As you can see, I’m everywhere. If photos of possessed ghost squirrels aren’t your thing, or you aren’t fascinated by the insanity that drives a person to build little buildings out of cardboard and wood, you no longer have an excuse. Follow me in the appropriate places, and I’ll try to make sure not to cross the streams.

If none of these options works for you and you’re still seeing stuff from me that you don’t want to see, I leave you with this:

The post Social Media Housecleaning appeared first on RealSQLGuy.

14 Dec 06:55

Fraud Detection with the SQL Server Suite Part 2

by Dejan Sarka

This is the second part of the fraud detection whitepaper. You can find the first part in my previous blog post about this topic.

My Approach to Data Mining Projects

It is impossible to evaluate the time and money needed for a complete fraud detection infrastructure in advance. Personally, I do not know the customer’s data in advance. I don’t know whether there is already an existing infrastructure, like a data warehouse, in place, or whether we would need to build one from scratch. Therefore, I always suggest to start with a proof-of-concept (POC) project. A POC takes something between 5 and 10 working days, and involves personnel from the customer’s site – either employees or outsourced consultants. The team should include a subject matter expert (SME) and at least one information technology (IT) expert. The SME must be familiar with both the domain in question as well as the meaning of data at hand, while the IT expert should be familiar with the structure of data, how to access it, and have some programming (preferably Transact-SQL) knowledge. With more than one IT expert the most time consuming work, namely data preparation and overview, can be completed sooner. I assume that the relevant data is already extracted and available at the very beginning of the POC project.

If a customer wants to have their people involved in the project directly and requests the transfer of knowledge, the project begins with training. I strongly advise this approach as it offers the establishment of a common background for all people involved, the understanding of how the algorithms work and the understanding of how the results should be interpreted, a way of becoming familiar with the SQL Server suite, and more.

Once the data has been extracted, the customer’s SME (i.e. the analyst), and the IT expert assigned to the project will learn how to prepare the data in an efficient manner. Together with me, knowledge and expertise allow us to focus immediately on the most interesting attributes and identify any additional, calculated, ones soon after. By employing our programming knowledge, we can, for example, prepare tens of derived variables, detect outliers, identify the relationships between pairs of input variables, and more, in only two or three days, depending on the quantity and the quality of input data. I favor the customer’s decision of assigning additional personnel to the project. For example, I actually prefer to work with two teams simultaneously. I demonstrate and explain the subject matter by applying techniques directly on the data managed by each team, and then both teams continue to work on the data overview and data preparation under our supervision. I explain to the teams what kind of results we expect, the reasons why they are needed, and how to achieve them. Afterwards we review and explain the results, and continue with new instructions, until we resolve all known problems.

Simultaneously with the data preparation the data overview is performed. The logic behind this task is the same – again I show to the teams involved the expected results, how to achieve them and what they mean. This is also done in multiple cycles as is the case with data preparation, because, quite frankly, both tasks are completely interleaved. A specific objective of the data overview is of principal importance – it is represented by a simple star schema and a simple OLAP cube that will first of all simplify data discovery and interpretation of the results, and will also prove useful in the following tasks. The presence of the customer’s SME is the key to resolving possible issues with the actual meaning of the data. We can always replace the IT part of the team with another database developer; however, we cannot conduct this kind of a project without the customer’s SME.

After the data preparation and when the data overview is available, we begin the scientific part of the project. I assist the team in developing a variety of models, and in interpreting the results. The results are presented graphically, in an intuitive way. While it is possible to interpret the results on the fly, a much more appropriate alternative is possible if the initial training was also performed, because it allows the customer’s personnel to interpret the results by themselves, with only some guidance from me. The models are evaluated immediately by using several different techniques. One of the techniques includes evaluation over time, where we use an OLAP cube.

After evaluating the models, we select the most appropriate model to be deployed for a production test; this allows the team to understand the deployment process. There are many possibilities of deploying data mining models into production; at the POC stage, we select the one that can be completed quickly. Typically, this means that we add the mining model as an additional dimension to an existing DW or OLAP cube, or to the OLAP cube developed during the data overview phase. Finally, we spend some time presenting the results of the POC project to the stakeholders and managers.

Even from a POC, the customer will receive lots of benefits, all at the sole risk of spending money and time for a single 5 to 10 day project:

  • The customer learns the basic patterns of frauds and fraud detection
  • The customer learns how to do the entire cycle with their own people, only relying on me for the most complex problems
    • The customer’s analysts learn how to perform much more in-depth analyses than they ever thought possible
    • The customer’s IT experts learn how to perform data extraction and preparation much more efficiently than they did before
    • All of the attendees of this training learn how to use their own creativity to implement further improvements of the process and procedures, even after the solution has been deployed to production
  • The POC output for a smaller company or for a subsidiary of a larger company can actually be considered a finished, production-ready solution
    • It is possible to utilize the results of the POC project at subsidiary level, as a finished POC project for the entire enterprise
  • Typically, the project results in several important “side effects”
    • Improved data quality
    • Improved employee job satisfaction, as they are able to proactively contribute to the central knowledge about fraud patterns in the organization
    • Because eventually more minds get to be involved in the enterprise, the company should expect more and better fraud detection patterns

After the POC project is completed as described above, the actual project would not need months of engagement from my side. This is possible due to our preference to transfer the knowledge onto the customer’s employees: typically, the customer will use the results of the POC project for some time, and only engage me again to complete the project, or to ask for additional expertise if the complexity of the problem increases significantly. I usually expect to perform the following tasks:

  • Establish the final infrastructure to measure the efficiency of the deployed models
  • Deploy the models in additional scenarios
    • Through reports
    • By including Data Mining Extensions (DMX) queries in OLTP applications to support real-time early warnings
    • Include data mining models as dimensions in OLAP cubes, if this was not done already during the POC project
    • Create smart ETL applications that divert suspicious data for immediate or later inspection
  • I would also offer to investigate how the outcome could be transferred automatically to the central system; for instance, if the POC project was performed in a subsidiary whereas a central system is available as well
  • Of course, for the actual project, I would repeat the data and model preparation as needed

It is virtually impossible to tell in advance how much time the deployment would take, before we decide together with customer what exactly the deployment process should cover. Without considering the deployment part, and with the POC project conducted as suggested above (including the transfer of knowledge), the actual project should still only take additional 5 to 10 days.

The approximate timeline for the POC project is, as follows:

  • 1-2 days of training
  • 2-3 days for data preparation and data overview
  • 2 days for creating and evaluating the models
  • 1 day for initial preparation of the continuous learning infrastructure
  • 1 day for presentation of the results and discussion of further actions

Quite frequently I receive the following question: are we going to find the best possible model during the POC project, or during the actual project? My answer is always quite simple: I do not know. Maybe, if we would spend just one hour more for data preparation, or create just one more model, we could get better patterns and predictions. However, we simply must stop somewhere, and the best possible way to do this, according to my experience, is to restrict the time spent on the project in advance, after an agreement with the customer. You must also never forget that, because we build the complete learning infrastructure and transfer the knowledge, the customer will be capable of doing further investigations independently and improve the models and predictions over time without the need for a constant engagement with me.

14 Dec 06:54

Optimizing Dimension Data Security

by tlachev

Dimension data security is a great feature of Analysis Services and one of the top reasons to strongly consider a semantic layer in your BI implementations, as I mentioned in my "Why Semantic Layer" newsletter. That's because the server automatically secures the cube at connect time so you don't have to implement your own security framework and secure every report query. If you are new to dimension data security, I covered it in various places including this article that I wrote for SQL Magazine a while back. One thing to be aware of is that the server applies dimension data security when the user sends the first query after the connection is established even if this query doesn't specifically request the secured dimension, e.g. SELECT NULL ON 0 FROM [CubeName]. Many things happen at this point (see the steps after "Dimension security is applied to all attributes" in this Mosha blog). This initialization sequence is the price you need to pay to have your entire cube secured. The initialization duration depends on many factors, including the size of the dimension, the size of the allowed set, and the cube script. However, you have some control over the initial performance hit or at least to offset it.

Let's consider a real-life scenario. A customer has a medium-size cube (about 50 GB) and dimension data security needs to be applied to an Account dimension whose dimension key attribute had about four million members. Specifically, the allowed set had to be applied on an attribute that had about 300,000 members. In this case, the security policies are stored in a factless fact table (for more information how dimension data security works with a factless fact table, see the second part of the above article). The initial initialization time for an allowed set of only two accounts was 9 seconds which is not so bad. However, if the allowed set return 20,000 allowed accounts, the user had to wait for 30 seconds to connect which can surely lead to user complaints.

Reducing the Autoexists Performance Hit

When the server applied dimension data security, it applies it to all attributes within the secured dimension. This behavior is known as Autoexists. In this case, the Account dimension had about 30 attributes. Although dimension security is applied on a single attribute (Level 3), the server automatically secures the rest of the attributes to allow only the members that exist with the allowed members of the secured attribute (if the allowed set returns 'USA', Autoexists allows only cities within USA). One way to determine how much time the server spends applying Autoexists is to enable and monitor in the Query Dimension event in the Profiler. Another way would be to remove all the cube script (except the first CALCULATE statement) and track the query execution time before and after.

As I said, the larger the allowed set and the dimension size, the more time will be spent in propagating security to the attributes in the secured dimension. To reduce this time, consider introducing a new dimension, e.g. Account Filter, that has as fewer attributes as possible. Ideally, it should include only the secured attribute although you might need to include the dimension key attribute so you don't have to make changes to the fact tables in order to join them to the dimension at a higher level. In our case, we tested three scenarios:

Scenario 1: Security applied on the original dimension

An allowed set of two members took 1.7 seconds in Autoexists. An allowed set 20,000 members took 13 seconds in Autoexists.

Scenario 2: Security applied on a new Account Filter dimension that had three attributes (dimension key, secured attribute, and another attribute required to meet the business requirements for security).

An allowed set of two members took 1.5 seconds in Autoexists. An allowed set 20,000 members took 3.5 seconds in Autoexists.

Scenario 3: Security applied on a new Account Filter dimension that had two attributes (secured attribute and another attribute required to meet the business requirements for security). This scenario required changes to the fact tables to bring in the secured attribute in order to join them to the Account Filter dimension.

An allowed set of two members took 1.2 seconds in Autoexists. An allowed set 20,000 members took 1.7 seconds in Autoexists.

Optimizing the Cube Script

This cube had about 3,000 lines, most of them basic calculated members (a best practice is to have calculations defined in the semantic layer instead of reports or elsewhere). The items to pay attention to in the script are static sets and default members (default members can be defined in the dimension itself or in the script). A static set is evaluated once per the user session. With regards to dimension data security, we've found that the server evaluates the static set in the context of the allowed members. If you turn the Execute MDX Script Current event to see how much time the server spends evaluating each line in the script. In our case, I've found that the following static set took some 10 seconds to evaluate with an allowed set of 20,000 members:

CREATE SET [MonthsWithInventory] AS Exists([Date].[Fiscal by Month].[Month].Members, , "Inventory");  

When I converted the set to dynamic (CREATE DYNAMIC SET), the overhead disappeared from the initialization time. However, be aware that during the static set evaluation the server warms the cache with the results of the static set. So switching to a dynamic set really saves you the cost of evaluating the Exists query which in this case was expensive. Consequently, the first query will execute faster. But it obviously means that the cache that would have been built up as part of evaluating that set is now not available for other operations. Therefore, subsequent queries that requests data by month might be slower.

What I've found also is that overwriting the default members contributes to the initialization times. If you have an option, remove explicit attribute defaults. Interestingly, trying to conditionally bypass the defaults didn't eliminate the overhead, such as using the following approach:

ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Account].[Active Flag], Default_Member = iif(IsEmpty(CustomData()), [Account].[Active Flag].&[Active], [Account].[Active Flag].DefaultMember);

 

Special thanks to Akshai Mirchandani on the SSAS team for shedding light in dark places.

14 Dec 06:53

Interview With Rick Bolesta of the PASS Board of Directors

by Andy Warren

Rick Bolesta and Neil Buchwalter of CA technologies are leaving the Board at the end of 2013. CA and Microsoft were the two ‘founding’ organizations when PASS was chartered and each has had two seats on the PASS Board since then. I caught up with Rick during the 2013 PASS Summit to ask him a few questions about his tenure on the board.

AW: Rick, when did you join the PASS Board?

RB: I joined about four months after the first Summit. The original representative from CA had a conflict and given my experience on other user group boards I was asked to represent CA on the PASS board. Neil joined about a year later.

AW: So you’ve been on the board…a long time!

RB: Right at 15 years

AW: That is a long time. What made you stay that long?

RB: In the beginning it was the excitement of being part of something new. Then it became exciting to be part of something with such great potential, and then (now) it was about being part of an organization finally growing into that potential.

AW: What was PASS like in the early years?

RB: Early conversations tended to be overly tactical. We would often get very deep into details on things that were way too tactical for a board meeting and in ways that weren’t productive. Since much of the strategy was driven by the management company at that time, board members did not question the strategy and were left to tactical discussions. 

AW: When and why do you think PASS has succeeded?

RB: It’s a combination of a few things. Back in 2005 the Board started to engage more on strategy, something that led us to change to our current management company. Microsoft re-engaged right about the same time and that helped too. Our Board members have gotten better at having conversations at the right level, due in part to having better board members and probably in larger part to being able to stand on the shoulders of their predecessors. I think SQLSaturday had a huge impact on PASS as well. By 2008 or 2009 it was apparent that PASS was growing tremendously. Ultimately, I attribute it to a community of great people and true grass roots efforts. It is the collective community that made PASS grow and become better.  There are people in the community that had a major impact on PASS but never served on the board.  They improved that way we do elections and serve the community at large.

AW: Rick, we served together on the Board for three years. During that time I noted that you and Neil both had an interesting (and for me, unexpected) approach to how you served – can you talk about that a little?

RB: Neil and I decided that we never wanted to be the tie-breaking vote on an issue. We would be part of the discussion, but we didn’t want to be perceived as having an agenda beyond the health of the organization – and in particular we were aware of how easily our efforts might have been seen as marketing for CA. As we stayed and grew, we also saw that we could add a lot of value by explaining to the Board the history behind some decisions. Not to say,  “we tried that and it didn’t work so don’t do it again” but rather “here is what happened before, now let’s talk about what is the right thing to do now”. We also focused a lot on governance, making sure that we followed our own rules.

AW: CA was a founding member of PASS. Why is that changing now?

RB: CA has evolved over the years and while SQL Server is still important to CA, we’re focusing in areas that are more encompassing of other technologies and not so SQL Server focused. So, I’d say the main reason is that our interests are no longer as well aligned as they were and because of that we don’t think it makes sense for us to have two seats on the Board – those should go to someone that is passionate and highly engaged with both PASS and SQL Server.

AW: Rick have you considered staying on in some sort of advisor role?

RB: Neither Neil nor I asked for an advisor position on the board so not really. The Board is always welcome to call Neil and I to discuss PASS issues and we hope to personally stay engaged with PASS, but seats on the Board should go to people that are looking forward, not looking back.

AW: Let’s change focus for a minute to something more recent. What did you think of the PASS election this year?

RB: I like to see a vibrant election. Over the past few elections we’ve seen candidates campaign in different ways and I think that is good – it’s all part of PASS growing. I thought the election went well.

AW: I had someone ask me this week about the next ‘big thing’ for PASS. Do you have ideas about what that might be?

RB: There may be a ‘big thing’, but I’d want to bet on pushing for incremental growth everywhere; more Chapters, more SQLSaturday’s, more members, more international efforts. I believe we will make a much bigger impact in membership in the emerging markets with the grass roots approach and by building on the foundation that is in place. I think the BA Conference is a great attempt to expand the PASS community, though I do wish it wasn’t limited to just SQL Server. Limiting the Summit to SQL Server makes perfect sense, that is the brand we built. The BA Conference is new and we could if we wished to open up to more products and vendors. I still hope that will happen.

AW: Rick, how has being part of PASS impacted your career?

RB: It’s always good to do something outside of work. It’s a way to avoid the ‘9 to 5er’ perception to start with, but it also brings the chance to learn different things. I worked with a lot of different people with different styles on the Board, a great learning experience. Learning to use influence instead of authority was a good lesson to learn.

AW: What advice do you have for PASS going forward?

RB: Stay focused on incremental growth and don’t move away from grass roots involvement. Finally, I have said to each new board…your most important job is to make sure we have the right people for the next board and your legacy depends on the future team you build.

AW: Thanks Rick!

The contribution Rick and Neil made is one that was probably only apparent if you were in a Board meeting. Having them present as the voice of history, as a steadying influence, as counselors, and especially their focus on governance was a huge plus for PASS. I was certainly more successful because of their input than I would have been without it. I’m sorry to see them leave, but pleased that they are leaving for the right reasons, in a way totally consistent with the way that they have served all these years.

14 Dec 06:53

Check for Instant File Initialization

by TiborKaraszi
Instant File initialization, IFI, is generally a good thing to have. Check out this earlier blog post of mine f you don't know what IFI is and why it is a good thing: blog . The purpose of this blog post is to provide a simple script you can use to check if you have IFI turned on. Note that the script below uses undocumented commands, and might take a while if you have a large errorlog file... USE MASTER ; SET NOCOUNT ON -- *** WARNING: Undocumented commands used in this script !!! *** -- --Exit...(read more)
14 Dec 06:52

Is There Interest in SQL Server Security Pre-Cons?

I’m very passionate about security, especially database security. As the numbers with regards to data breaches continue to climb, this is become even more important to me. I’ve been affected personally by data breaches, as have many others. For instance, I’ve had to change out my credit cards due to payment processor breaches, I was affected by the South Carolina Department of Revenue breach, and I’m sure there are others, maybe ones that weren’t reported, that I was affected by as well. I enjoy speaking on security, but a 45-75 minute session barely scratches the surface when it comes to any security topic. I’d love to spend more time covering more information, diving down into more details, etc., for those wanting to learn. The best way to do that is probably through a pre-con at a SQL Saturday. I’ve given half day and full day sessions before, primarily to IT auditors, and I’m thinking the same sort of time length is appropriate to convey enough information to make it worth paying for. Here’s a sample agenda from one of my half day courses to give you an idea of what I’m thinking about (this is from a SQL 2000-2008R2 class):

  • Hour 1
    • General Security Principles We Follow
    • Hardening OS and SQL Server – Surface Area
    • Access SQL Server and its Databases
  • Hour 2
    • SQL Server’s Permission Model
    • Changes from SQL Server 2000 to 2005
  • Hour 3
    • Auditing using Triggers
    • Server Side Traces
    • Audit Object
  • Hour 4
    • Built-In Encryption Options

If you think you’d be interested in such a pre-con, especially if you’re a SQL Saturday organizer, comment on this post or, better, send me an email at brian {dot} kelley {at} sqlpass {dot} org (note the spelling on the last name as kelly will cause it to bounce).

14 Dec 06:50

Revolutionizing City Planning in the 21st Century with Windows Azure, HDInsight, and SQL Server

by SQL Server Team

If you’ve ever played a video game where you manage a virtual city, you understand how real-time insight into services including emergency response teams and transportation is needed for effective city planning. Until recently, the technologies just weren’t available to support this kind of detailed “big picture” view enjoyed by gamers. However, now that Big Data business intelligence (BI) solutions are a reality, Barcelona, Spain is working to achieve transformative insight so that it can better meet the needs of its citizens. 

To test the feasibility of a Big Data BI solution, Barcelona created a pilot that runs on a hybrid cloud based on Windows Azure, Windows Azure HDInsight Service, and Microsoft SQL Server 2012. With it, users can view real-time BI that combines petabytes of existing data in the city’s systems with new, unstructured public data sources such as Twitter, app log files, and GPS signals from cell phone providers. The BI is served up via mobile apps, dashboards, custom reports, and data services.

The variety of technology choices in the Microsoft platform simplified development. For example, engineers met the diverse needs of users and devices using HDInsight and built-in tools in SQL Server, Microsoft Office, and Visual Studio. Engineers were also able to manage the project’s varied data and cost requirements with Apache Hadoop and Azure’s other storage options, which you can read about here.

Everyone wins with this kind of solution. City employees are more productive because they quickly see how services are working from “big picture” views that include detailed numbers and social media feedback. Citizens enjoy a better quality of life because services like emergency teams, transportation, schools, and festivals are customized to meet real needs. Businesses can also increase success by quickly recognizing investment opportunities in specific neighborhoods. Other cities also benefit because they can adopt this repeatable smart-city infrastructure to transform insight, lifestyles, and economic success.

You can learn more about Barcelona’s pilot solution by reading the case study and watching the video here.   

14 Dec 06:49

Microsoft a Leader in Gartner’s Magic Quadrant for Operational Database Management Systems

by SQL Server Team

This is one of the best times to be working in SQL Server. I just came from the O’Reilly Strata + Hadoop World conference this week, where there was a great dialog about advances in data management techniques. We’re already the world’s most deployed database, we just released the second technology preview for SQL Server 2014, and if you saw Microsoft’s recent financial results you know SQL Server continues to outpace the market: revenue grew double-digits, with SQL Server Premium revenue growing more than 30%. And the SQL Server momentum is not stopping.

On October 21st, Gartner published the Magic Quadrant for Operational Database Management Systems. In the opening summary, Gartner states, “The operational DBMS market (formerly OLTP) is evolving dramatically, with new, innovative entrants and incumbents supporting the growing use of unstructured data and NoSQL DBMS engines. Information management leaders must understand the market implications affecting DBMS technology decisions.”

Microsoft is truly honored to be included in the Leaders Quadrant* for this Magic Quadrant based on our completeness of vision and ability to execute.

I couldn’t agree more with Gartner, and our customers agree as well. The early previews for SQL Server 2014 have been downloaded more than 50,000 times and are being previewed by more than 100 customers; you can download the preview here. Check out their case studies to read more about how customers such as SBI Liquidity and TPP are taking advantage of the in-memory capabilities in SQL Server 2014. Ferranti is a great example of using both In-Memory OLTP and HDInsight technologies to process a continuous data flow of up to 200 million measurement channels, making the system fully capable of meeting the demands of smart meter technology. Go here  to read the full Gartner report. 

Eron Kelly
General Manager
SQL Server Marketing

*  Gartner, Magic Quadrant for Operational Database Management Systems by Donald Feinberg, Merv Adrian and Nick Heudecker, October 21, 2013.

This graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available at http://www.gartner.com/reprints/server-tools?id=1-1M9YEHW&ct=131028&st=sb.

Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings. Gartner research publications consist of the opinions of Gartner's research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose. 

14 Dec 06:49

Creating SQL Server Agent Alerts for Critical Errors

One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.

One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQLskills own Paul Randal previously wrote about this. SQL Agent Alerts will fire when certain types of errors occur, and you can configure them to notify a SQL Operator when this happens. This will let you find out about these types of problems much earlier than you otherwise probably would, which is a good thing!

The code in this script is an updated version of the script that I included in my article that automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. I have also recently added an additional alert for Error 832, and for Errors 855 and 856 (if you are running SQL Server 2012 Enterprise Edition).

SQL Server 2012 Enterprise Edition has a new feature called Memory Error Recovery. It can detect (and sometimes correct) when clean database pages have become corrupted in memory. You need to have Windows Server 2012 or newer, and you must have a processor (such as an Intel Xeon 7500 series or Intel Xeon E7 series) that supports this functionality. If you have all of this, you will see this message in your SQL Server Error Log:

Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.

There are some more details about this in this KB article: Using SQL Server in Windows 8 and Windows Server 2012 environments. There is a PowerPoint deck about this feature here.

In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit. You need to supply a valid SQL Operator name for my script to work correctly.

The post Creating SQL Server Agent Alerts for Critical Errors appeared first on Glenn Berry.

14 Dec 06:48

CISCO VPN Error 442 and Windows 8.1

by arcanecode

After upgrading to Windows 8.1 I had issues running Cisco VPN software. When attempting to run I got an error 442. (Note this applies to Cisco VPN, not Cisco AnyConnect.) As a first step in troubleshooting I ensured that I was on the latest version, 5.0.07.0440.

As I was already on the latest version, I began to do some web searching. Likely you, as I did, found many blog posts referring to a fix for the registry. In case you haven’t seen it, the basic instructions are:

1. Open RegEdit.

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA

3. If the DisplayName does not already read:

Cisco Systems VPN Adapter for 64 bit Windows

Change it so matches what you see above. Some installs have some “gibberish” on the front, this should be removed. In my case it was already set to what you see above, so it was on to the next step. After some more searching and experimentation, I finally came upon a solution that worked for me.

Open Windows Explorer, and navigate to C:\Program Files (x86)\Cisco Systems\VPN Client. Right click on the first .exe you find, in my case cisco_cert_mgr.exe and pick Properties from the menu. Switch to the Compatibility page.

Check on “Run this program in compatibility mode for:” and pick Windows 7. Then at the bottom, check on “Run this program as an administrator. Then click on OK.

image

 

Repeat this for every exe in the folder.

image

Unfortunately you can’t apply these in mass, you have to do these one at a time. There’s only a handful though so it shouldn’t take long.

After you are done, reboot. Before you run the VPN software, verify the Cisco network connector is not active by going to Control Panel, Network and Internet, Network Connections and validate the Cisco Systems VPN Adapter for 64 bit Windows is Disabled.

Note after you connect this will become Enabled, you just want to be sure it is disabled before you connect. If you see multiple connections for the Cisco adapter, you will need to delete the excess ones, I’ve also seen suggestions to uninstall the Cisco VPN, make sure all the Cisco connections are deleted, then reinstall. I didn’t have to go through this though, so your mileage may vary.

Also note that after you reboot, you may see a prompt asking if you want to run the Cisco service as Administrator, naturally you’ll say Yes. Then go run the Cisco VPN client again, confirming you want to run as an Admin, and you should be good to go!


14 Dec 06:48

Staying Current on Technology for your Organization

by BuckWoody

As a Technical Professional, your company, firm, group or organization looks to you for guidance on their technical systems. You're expected to apply the best technology solutions that you can to a given business or science problem or opportunity.

That can be quite difficult to do. If you are in a vertical knowledge role, expected to know a lot about a given technology, you have fairly clear boundaries on keeping current with your area. My approach when I'm in a role such as developer, database administrator or systems administrator is to leverage these sources:

When I'm in a role that is more breadth-oriented, such as I am now, I'm expected to know several technology stacks, not just one in depth. This is common in Architect roles, lead developers, researchers and analysts. And it's also true in "The Cloud", specifically Windows Azure for me in my current Worldwide Role here at Microsoft. Since Windows Azure is a platform that contains everything from IaaS (VM's, Storage and more) to PaaS (including Open-Source languages and Platforms) and even a kind of SaaS (such as Web Sites and Hadoop or HDInsight) it can be difficult to stay up to date.

So in addition to the resources listed above - meaning that those things are assumed, I have two primary approaches: Feature/Function, and Problem/Solution (or Opportunity/Solution if you prefer).

Feature/Function

In this approach (again, assuming that I've used all the resources listed above first) I evaluate all of the components within a platform or system to discover:

  1. What they do
  2. What I know about that
  3. What I do not know about that

For instance, I use the Windows Azure Portal screen to simply walk through every single click, and then I create a OneNote ontology answering those three questions. From there I research until I have references and an understanding of what the feature does and where it can be used.

With that knowledge laid out, I then locate a source that keeps me up to date on the changes for that platform, and then I evaluate that source when it is released, adding to the OneNote notebook. For instance, for Windows Azure, I use this source: http://blogs.msdn.com/b/davidmcg/

Problem/Solution

While you can evaluate a system by a component approach - and I usually do - it's often helpful to discuss where a platform can be used to solve a business or organization problem, or if you want to flip that around, present an opportunity. It's really the same thing, but does color the way you approach the material. For instance, the first two responses are problem-focused and the others are opportunity focused.

Problem: Glass contains 50% water

  • Optimist restatement: Glass half-full
  • Pessimist restatement: Glass half-empty
  • Engineering restatement: Glass designed improperly - I'll architect a smaller glass
  • Opportunist: I'll just drink the water while you guys sort this out

And of course the chemist thinks there is no problem at all - the glass is completely full (50% H2O with 50% Oxygen and other trace gasses). In any case, instead of focusing on the platform or system, I'll focus on where I might use it - the problem or opportunity. I'll lay out a matrix with decision points that are important to the organization and then lay in various options, applying them to those criteria. Whichever solution meets the criteria is the one I'll dive into in more detail.

It's important to note that other than perhaps the Spork, there are very few tools that are perfect for their opportunity. There are always tradeoffs, and some of those are not necessarily technical. In an architectural role there are sustainability and practicality aspects, as well as budgetary and political aspects.

14 Dec 06:48

Building The Redneck Treadmill Desk

by tracy

redneck_desk

Don’t tell Brent Ozar, but here’s another DBA writing another helpful article about shrinkage. Before he gets bent out of shape and writes another rant about shrinking database files, let me clarify one thing – this article isn’t about database shrinkage, it’s about DBA shrinkage.

Almost a year ago, while meeting Grant Fritchey, Merrill Aldrich, and a few other folks for dinner at the Mall Of America, I picked up a Fitbit One. Easily the best $100 gadget that I’ve ever purchased. Since that night, I’ve walked or run 3,118,471 steps, climbed 3,652 flights of stairs, and traveled 1,391 miles on foot. More importantly, I’ve lost 40 pounds, weighing in this morning at 149, down from the 189 that I started at. Serious DBA shrinkage.

A lot of those steps and miles took place right in my own basement, on the 10-year-old treadmill that sat in the corner for several years. Combining that treadmill with the Roku that is hooked up to the basement television, I’ve walked through all nine seasons of The X-Files, several episodes of Star Trek, The Walking Dead, and a dozen or so full-length movies. A few weeks ago I decided to try a different combination – walking and getting work done at the same time. For that, I needed a treadmill desk.

treadmill_roughYou can buy some really nice treadmill desks, but they tend to be rather pricey. Since I already have the treadmill, and I’m relatively handy when it comes to building things, I decided to try making my own. The first version, my “rough draft” if you will, consisted of three pieces of pink styrofoam insulation laid across the armrests of the treadmill. Not pretty by any means, but quite functional.

As I said, this was my rough draft, a test to see if I would actually be able to work while walking on the treadmill, and to see if it would be comfortable to do so. Both have proven to be true – I’ve spent hours walking that would otherwise have been spent sitting in a chair with a computer in my lap. I’ve spent the same amount of time, maybe more, using the computer, getting work done, while at the same time getting a lot of physical activity.

I affectionately called this my “redneck treadmill desk”, but I decided this weekend that it was time to dress it up and make it a little less redneck. I wouldn’t want the family dropping my for a visit and deciding to take it with them.

What follows is a step-by-step guide to how I built my refined redneck treadmill desk. Should you choose to follow these, some steps will be different for your treadmill, but the basic concept should be the same.

I Want Wood

The main goal to refining the redneck treadmill desk was to cover up that pink styrofoam with something. The foam is nice because it’s light and easy to cut, but it’s ugly. The obvious, easy choice for material was wood, so I went shopping for some lumber.

1. I considered one of those pre-finished, laminated shelf boards, but since my plan was to glue the styrofoam to the board, I had concerns about the glue bonding with the laminate finish. I chose instead to go with unfinished wood – a 1″x12″ shelf board for the top, and 1/4″x6″ for the front and the sides.
2. As previously mentioned, I used pink styrofoam insulation for the underside of the desk. I chose this material because it’s light, easy to cut, and I just happened to have some lying around from an unrelated project.
3. To hold everything together, I used to different types of glue. For all of the wood-to-wood joints, I used normal yellow wood glue. For all of the styrofoam-to-styrofoam or styrofoam-to-wood joints I used a special foam adhesive from Loctite.
4. The first step of the actual assembly is to glue the shelf board onto a piece of pink styrofoam. Ideally, the piece of foam will be longer and wider than the shelf board – it will be to trim off the extra after the glue has dried. Apply a generous amount of the foam adhesive to the shelf board.
5. Place the shelf board on top of your piece of pink foam, adhesive side down (obviously). Put something heavy on top of it, and call it a day. The glue needs to dry overnight.
6. After the glue has dried overnight, it’s time to trim off the excess foam from around the edges. I used a hobby razor saw for this. Suggestion – do this step outside, because it will make a mess.
7. Turn the shelf board onto its edge, then run a bead of the foam adhesive along the exposed edge of the pink styrofoam, and a bead of yellow wood glue along the exposed edge of the shelf board.
8. Place one of the 1/4″ facing board along the edge of the shelf assembly. To aid it assembly, I drove 4 small finishing nails through the facing board into the shelf board.
9. From the second 1/4″ facing board, cut two pieces that are each 12″ long. Consider this an excuse to use the table saw that has been sitting in the corner of the garage, ignored, for the past two years.
10. Stand the shelf assembly on-end. Apply a bead of foam adhesive and a bead of wood glue to the exposed edges of the shelf board and the pink styrofoam. Put one of the 12″ pieces that you cut from the 1/4″ facing board onto the end of the shelf. As with the front edge, I used finishing nails to help secure this in place. Turn the shelf over onto the other end, and repeat this process.
11. If you’ve done everything correctly, you should now have what is essentially a three-sided box that looks something like the one in this photo.
12. Now it’s time to take the shelf into the house for a test-fitting. Put it onto the treadmill armrests, centered.
13. Using a black Sharpie, or some other writing utensil, mark the INSIDE of each armrest on the pink styrofoam.
14. Take the shelf back outside, and cut two more pieces of pink styrofoam. Once piece needs to be the full length of the shelf. The other piece needs to fit between the two marks that indicate the inside edge of the armrests. Using the foam adhesive, glue them to the underside of the shelf as seen in this photo. The full-length piece goes in first, with the shorter piece on top of it, centered between the armrest marks. When the shelf is installed on the treadmill, this shorter piece will sit between the armrests. This needs to sit overnight for the glue to dry, so it’s time to call it a day.
15.
After letting the glue dry overnight, it’s time for the moment of truth. Take your awesome new treadmill desk into the house and put it on the treadmill. As I mentioned in the previous step, that shorter piece of pink styrofoam will fit between the armrests. This is to prevent side-to-side movement of the shelf.
16.
That’s it! You’ve done it! You’ve built yourself a dirt-cheap-yet-fully-functional treadmill desk. Slap a coat of paint on the thing and it won’t look half bad.

Now, join me in a Tim the ToolMan Taylor gruntfest…

The post Building The Redneck Treadmill Desk appeared first on RealSQLGuy.

14 Dec 06:47

CIOs: Stop Mandating Training

by merrillaldrich
I love to learn about new technology, and I especially love a long deep-dive technical session with a real expert or a well-crafted, inches thick technical book. Even if either one is expensive. Learning is probably my favorite thing to do. Yet I stand before you with an appeal: Stop “sending people to training.” Why would I say such a thing? Because failure is baked right into that very phrase: “sending people to training.” Death by Training Most of us in the IT world have probably experienced this...(read more)
14 Dec 06:46

Limiting error log file size in SQL Server 2012

by Paul Randal

It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.

One thing that hasn’t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!

While in my post-con workshop at SQL Intersection in Las Vegas last week, Jan Kåre Lokna (a former Immersion Event attendee from Norway) discussed some code he’s been experimenting with and I just heard from him that he got it to work.

The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

I’ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with DBCC WRITEPAGE :-)

You can read a more in-depth description on Jan’s blog here.

The post Limiting error log file size in SQL Server 2012 appeared first on Paul S. Randal.

14 Dec 06:45

The future of business in the era of the customer

by Jeffrey Meisner

The following post is from Kirill Tatarinov, Executive Vice President, Microsoft Business Solutions.


This is an amazing week celebrating great milestones in our Microsoft Dynamics business solutions family.

On Monday at our Microsoft Dynamics CRM Global Premier launch event, digitally attended by more than 70,000 people, we displayed what we truly mean when we say we are changing the game in CRM. Three innovative and successful companies – Metro Bank, Pandora and Servcorp – all shared with us how important it is for businesses to better engage with and nurture their customers. Moreover, they all stressed how a uniquely differentiated Microsoft Dynamics CRM 2013 will help them win the hearts and minds of their customers.

On Tuesday at the Convergence EMEA event in Barcelona, more than 2,700 customers and partners attending live, and some 50,000 more virtually, heard directly from senior leaders at major global companies on how they are using Microsoft Dynamics to transform their businesses. We also announced new breakthrough capabilities in the next update of our ERP solution, Microsoft Dynamics AX 2012 R3 (with details in today’s press release).

We also discussed how the world of business is experiencing a dramatic change caused by the confluence of several mega trends in the global economy, demographics and technology.

Consider this: By 2015, IDC predicts there will be 1.3 billion mobile workers. Today, the average worker carries more than three devices while travelling. Ninety two percent of workers believe their smartphones should be enabled for both work and personal use, causing a massive impact of consumerization in IT and business.

Businesses are reacting. Sixty percent of companies are already adapting their IT infrastructure to accommodate employees’ personal devices rather than restrict employee use, according to Gartner. In addition, 67 percent of businesses are using social media for work.

This empowerment of the people at work knows no geographic boundary and is, in many ways, a great equalizer. According to forecasts, more than half the world’s biggest companies will come from emerging markets by 2025; up from just five percent in 2000. Emerging markets are changing where and how the world does business.

The altered business landscape is accentuated by the influx of a new generation of young and socially engaged consumers who have no deep-seated brand loyalty, emphasize lifestyle over other status markers, value autonomy, and look to explore, experiment and come up with different ideas about how things work.

With a constant connection to the Internet and their social networks, today’s customers are informed, smarter, give feedback that can go viral and are therefore more empowered than ever before. We have truly entered the era of the customer.

Customers have the power. Research shows that more than 90 percent of unhappy customers do not complain, they simply leave and never come back. Seventy percent of buying experiences are based on how the customer feels they are being treated. This is not a zero-sum game and rather than plot to take power away from their customers, successful businesses today will embrace this new era and look to ways to better engage and nurture their customers to build long-lasting relationships.

So, what does this mean for the future of business? What should we take away from all of this? For organizations to adapt and develop a new way of doing business, a new way of leading and a new way of engaging with today’s customers, they need to transform in three important ways.

First, businesses must change from the inside, and humanize their organization. Today’s mobile, digital, connected economy needs a different kind of leadership. Smart, dynamic businesses will start to take a look at their existing cultures and even consider some different, less hierarchical understanding of what leadership means if they are going to successfully adapt to this new world. Those who confine social just to sales and marketing will soon be at a huge disadvantage to those that integrate the human-powered principles to their leadership.

Second, leaders must better align their businesses to meet their customer’s needs. It starts with providing great experiences. Many businesses recognize this. Eighty percent of firms would like to use customer experiences as a form of differentiation. Moreover, many see the value of highly integrated, end-to-end business solutions to help them get there. The advancements we have made in Microsoft Dynamics CRM 2013 and the capabilities we are delivering with Microsoft Dynamics AX 2012 are specifically designed to help businesses align with their customers’ needs and deliver amazing experiences.

Third, businesses must unite to succeed. The question that plagues all businesses: Who owns the customer? Is it sales, the CEO/President or the CMO? In this new era of the customer, the answer is all of the above. Businesses need to change their thinking and strive toward an organization where everyone is aligned to the customer with an organizational culture that places the customer at the center of everything they do. Businesses must unite their people, their processes and their technology to enable a single view and focus on the customer. This is of paramount importance!

This week, at the Dynamics CRM 2013 launch and Convergence EMEA events, we celebrate those businesses that are succeeding, that are thriving in this era of the customer. Through people-centric leadership and intuitive, agile business solutions, they are the ones who are bringing their organizations together, who are uniting their front-end operations with their back-room infrastructure. In my keynote this morning, I was honored to speak to a number of these organizations – BioCop, BoConcept, Carrefour, Chanel, and Heineken – about how they are transforming their businesses. They are just a few examples of impressive companies focused on one thing – delivering amazing customer experiences that will turn smart, independent and connected customers into loyal fans! And we are proud to serve these and hundreds of thousands of other organizations around the world with our Microsoft Dynamics business solutions, helping every business become a united Dynamic Business.

14 Dec 06:45

In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables

by SQL Server Team

Starting with SQL Server 2012, the new offering of AlwaysOn Availability Groups and AlwaysOn Failover Cluster  Instances (FCI) suite of features have enabled Tier-1 customers with mission critical applications to achieve their availability goals with SQL Server with an easy to deploy and manage solution. SQL Server 2014 builds on this success and offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid scenario with Windows Azure.  

In-memory OLTP is also targeting mission critical applications to deliver up to 30x better performance, and is integrated well with High Availability offerings SQL Server provides. The sections below review each of the High Availability offerings in the context of the in-memory OLTP solution. SQL Server 2014 offers four high-availability choices to customers as follows:

  • AlwaysOn Failover Cluster Instances (FCI): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

In-Memory OLTP is fully integrated with FCI.  A database with memory-optimized tables can be configured and managed similar to how you would manage a database with no memory-optimized objects. However, memory-optimized tables in the database will likely add to the recovery time because these tables need to be loaded into memory before the database can be accessed. In-memory OLTP engine loads data/delta file in parallel to minimize the time taken. The time to load the memory_optimized tables depends upon the size of memory-optimized tables, the number of data/delta files, IOPS available to read data/delta files and number of CPUs/Cores available. It is a best practice to configure SQL Server instances with same amount of memory as, unlike for disk-based tables, insufficient memory will cause database recovery to fail on the new instance.  Please refer to How to manage your Memory for In-Memory OLTP for managing memory for database with memory-optimized tables

  • AlwaysON Availability Groups:  SQL Server 2014 allows up to 8 secondary replicas including 2 sync secondary replicas with options to auto-failover. You can offload reporting workloads and database/log backups to one or more secondary replicas. This allows all resources on the primary replica to be solely used by the production workload leading to increased throughput.  Each replica has its own copy of the databases in the availability group

In-memory OLTP is fully integrated with AlwaysOn Availability. You can setup a database with memory-optimized tables in an availability group following exactly the same step that you would for a database without memory-optimized tables. Key points

    • Impact on failover time: The memory-optimized tables are maintained in-memory and kept up-to date by the redo thread as it processes transaction log records. This ensures that the failover to secondary replica will not incur additional overhead of loading memory-optimized tables’ in-memory. Hence, unlike with FCI, there is no impact to failover time.
    • Readable Secondary:  Querying memory-optimized tables both with natively compiled stored procedures and with SQL InterOP is supported on secondary replica. Unlike disk-based tables, the access to memory-optimized tables is done using optimistic concurrency therefore there is no need to map of isolation levels to snapshot isolation level done when accessing these tables. For this reason, reporting workload accessing memory-optimized tables are run without any isolation level mapping.  The restrictions on isolation levels and hints are the same both on primary and secondary replica. Please refer to Guidelines for Transaction Isolation Levels with Memory-Optimized Tables for details. This ensures any query or reporting workload that can be run on secondary replica will run without requiring any changes on the primary replica and vice-versa.  However, independent of the isolation level specified for reporting workload, SQL Server provides only read-committed isolation level consistency on secondary replicas for memory-optimized tables.  Please refer to Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for details. In CTP2, you need to enable TF-9989 to query in-memory OLTP tables on the secondary replica.
    • Non-durable tables: AlwaysOn Availability groups use transaction log transport to populate tables on the secondary replica. Since the changes to non-durable tables are not logged, these tables are not populated on the secondary replica. However, their schema will exist on replicas as part of the objects contained within the database.
    • Performance:  There are two aspects of performance. First, with the increased transactional throughput with in-memory OLTP, can the secondary replica keep up?  The short answer is that it will depend on the workload. A workload with high transactional throughput can put pressure on the log transport as well as on redo thread. With in-memory OLTP, to apply changes to memory-optimized tables, the redo thread will not incur IO related latency like it does for disk-based tables because the data is already in-memory. Second is the performance of reporting workload on the secondary replica. Its performance should be comparable to the performance on the primary replica. One limitation is that there is no auto-stats update on memory-optimized tables. You will need to manually update the statistics on the primary replica to generate the up to date statistics which can then be available on secondary replica after redo thread processes the log records associated with the statistics. Please refer to Statistics for Memory-Optimized Tables for details.
  • Log Shipping: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

In-memory OLTP: Log shipping is fully supported on database with memory-optimized tables. A database with memory-optimized tables can be configured and managed similar to how you would manage a regular database

  • Transactional Replication:  It is a popular solution for offloading read workload one or more subscribers especially when only a subset of data, not the whole database, is needed on the subscriber. It is also used to a lesser extent for High Availability.

In-memory OLTP: Replication is supported on databases with memory-optimized tables but the limitation is that you cannot use a memory-optimized table as an article in a publication or as part of a subscription. We will look into addressing this in a future release. In spite of this restriction, you can use memory-optimized tables in read-scale scenarios for read-mostly workloads as described below

Scenario:  Let us say you have couple of disk-based tables t_disk1 and t_disk2 that are accessed heavily by read workloads. For your workload, you want to replicate these two tables on the subscriber however you want read-workload to run memory-optimized tables. You can achieve this by creating corresponding memory_optimized t_memopt1 and t_memopt2 and populating them using DML triggers on disk1 and t_disk2 tables.  Any insert/delete or the update of the row, the triggered action will update the t_memopt1 and t_memopt2 accordingly. Essentially, you have two copies of the same table, one is disk-based and other is memory-optimized. Now you can direct your read-workload to the memory-optimized tables.

14 Dec 06:44

Inside the Storage Engine: How are allocation unit IDs calculated?

by Paul Randal

It’s been a long time since I’ve written a post about pure internals, but every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page.

When DBCC PAGE dumps a page header’s contents, it does the necessary calculations and metadata look-ups to be able to tell you the allocation unit ID, partition ID, relational object ID, and relational index ID. Basically everything prefixed with ‘Metadata:’ in the DBCC PAGE output below is NOT stored on the page itself:

Page @0x00000004ED8A2000

m_pageId = (1:445)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 97    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044284928
Metadata: PartitionId = 72057594039304192                                Metadata: IndexId = 0
Metadata: ObjectId = 599673184      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8069
m_freeData = 121                    m_reservedCnt = 0                   m_lsn = (225:443:22)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

The formula is as follows:

  • Take the m_indexId and left-shift by 48, giving value A
  • Take the m_objId and left-shift by 16, giving value B
  • AllocUnitId = A | B (where | is a logical OR operation)

Using the page above:

  • A = 256 << 48 = 72057594037927936
  • B = 97 << 16 = 6356992
  • AllocUnitId = 72057594044284928

You can do this using SQL Server using the POWER function as a left shift of X bits is the same as multiplying by 2-to-the-power-X:

SELECT 256 * CONVERT (BIGINT, POWER (2.0, 48)) | 97 * CONVERT (BIGINT, POWER (2.0, 16));
GO

And then you can perform the various look-ups using sys.system_internals_allocation_units and sys.partitions like so:

SELECT
	[a].[container_id] AS [Partition ID],
	[p].[object_id] AS [Object ID],
	[p].[index_id] AS [Index ID]
FROM sys.system_internals_allocation_units [a]
JOIN sys.partitions [p]
	ON [p].[partition_id] = [a].[container_id]
WHERE
	[a].[allocation_unit_id] = 72057594044284928;
GO
Partition ID         Object ID   Index ID
-------------------- ----------- -----------
72057594039304192    599673184   0

And you can see that the values match the DBCC PAGE output.

To convert from an allocation unit ID to what you should see in the DBCC PAGE output:

  • m_indexId = AllocUnitId >> 48
  • m_objId = (AllocUnitId – (m_indexId << 48)) >> 16

The T-SQL for this involves floating point math as we need to use the reciprocal of POWER:

DECLARE @alloc BIGINT = 72057594044284928;
DECLARE @index BIGINT;

SELECT @index =
	CONVERT (BIGINT,
		CONVERT (FLOAT, @alloc)
			* (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
	);
SELECT
	CONVERT (BIGINT,
		CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
			* (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
	) AS [m_objId],
	@index AS [m_indexId];
GO

 

m_objId              m_indexId
-------------------- --------------------
97                   256

An example of when you might use this information/code is during programmatic analysis of a corrupt database that DBCC CHECKDB cannot process to allow you to extract data as a last resort.

Enjoy!

The post Inside the Storage Engine: How are allocation unit IDs calculated? appeared first on Paul S. Randal.

14 Dec 06:44

SQL Server - Joining database on secondary replica resulted in an error

by Kanchan Bhattacharyya

Hi Friends,

 

Always On Availability Groups provides an enterprise-level alternative to database mirroring and it gives organizations the ability to manually or automatically fail over a group of database(s) as a single unit with support for up to four secondary(s). Many organizations at present are opting for this excellent feature as their HA solution that was shipped with SQL 2012.

In this blog post I’ll discuss about an issue that one of my friends came across while implementing ‘Always On’ in one of his fresh Windows Server 2012 environments. At the final step of configuration the wizard failed to join respective database on secondary replica. It popped up following message box however I extracted error message for reference.

AlwaysOn Error

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Joining database on secondary replica resulted in an error. 

(Microsoft.SqlServer.Management.HadrTasks)

------------------------------

ADDITIONAL INFORMATION:

Failed to join the database 'YourDatabaseName' to the availability group 'YourAvailabilityGroup' on the availability replica 'Servername\Instance'.

(Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)

&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExc

eptionText&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

As configuration failed, you will observe that Primary replica is Online however Secondary replica is in Offline state with a red mark on SSMS. When tried joining secondary replica to the availability group following message box was displayed by the wizard;

Joining Replica Error

As mentioned before, we were trying this setup on a new Windows Server 2012 VM as such I thought of checking which port is used by Availability Group and when checked, it was the default port i.e. 5022.

AvGroup Port

 

Important point is to keep this port opened on all Nodes in the Windows Firewall and it wasn’t the case here. So, we did that i.e. allowed 5022 passing through firewall and end result was all good. Same requirement is mentioned on MSDN which you can read here.

 

If you liked the post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

Have a SQL Server question? Join the fastest growing SQL Server Facebook group at: http://www.facebook.com/groups/458103987564477/

 

Regards,

Kanchan 

14 Dec 06:43

Transparent Data Encryption’s Dirty Little Secret

by Tracy McKibben

xray_ad_banner

Remember when you were eight years old, how exciting it was to order some of those amazing products that were advertised in the back of comic books? Sea Monkeys, your very own nuclear sub,  X-Ray glasses? Good stuff…

Remember how utterly disappointed you were when the crap that you ordered arrived, and the nuclear sub turned out to be three inches long, made of plastic, and fueled by baking soda? Those little black specks floating in water that looked nothing like monkeys? Yeah, good stuff alright…

Remember when SQL Server 2008 was still yet-to-be-released, and promised us things like built-in encryption, built-in compression of both data and backups? Finally, we could stop using third-party tools to fill these needs. No more Litespeed, no more Encryptionizer (no offense to either intended), it’s all available, right there in the box. Yeah, GOOD STUFF!

Take a walk with me down memory lane…

We’ve just upgraded, and naturally we want to start pushing buttons and turning knobs. What should we do first? Oh, how about a backup? Yeah, let’s try out the new backup compression!

-- First, we'll do a normal, uncompressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\AdventureWorksUncompressed.BAK'
GO

-- Next we'll do a compressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\AdventureWorksCompressed.BAK'
WITH COMPRESSION
GO

-- How does that compare to the uncompressed backup?
SELECT physical_device_name, backup_size, compressed_backup_size
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily
 ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.database_name = 'AdventureWorks2012'
 AND backupmediafamily.physical_device_name 
  IN ('C:\SQLBackups\AdventureWorksUncompressed.BAK',
      'C:\SQLBackups\AdventureWorksCompressed.BAK')
GO

From which we see this:

transparent data encryption

Holy downsizing, Batman, it worked! The compressed backup is 75% smaller than the uncompressed one. That’s a huge space savings! I’m sold – backup compression for everyone, it’s the new standard for our SQL Server backups.

Now let’s turn on encryption, because we want to keep our database safe. It’s a simple process, which I’ve written about before. Following the instructions in that post, we encrypt the AdventureWorks2012 database. Super-simple.

Remember how that felt? Wham! Bam! Kapow! Just like that, we’ve eliminated the need for two different third-party products from our environment. The boss is gonna be pleased! You confidently fire off an email asking him to stop by your desk so that you can show him your awesome new toys.

transparent data encryption

Your Nuclear Sub Has Arrived

Within minutes your boss walks over – “What’s up?” he says. You reply “You’ve gotta see this. Backups are compressing down to 25% of the original database size, AND I can encrypt the databases, without any extra software.”

You repeat the same backup compression test that you did previously:

BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\EncryptedAdventureWorksUncompressed.BAK'
GO

-- Next we'll do a compressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\EncryptedAdventureWorksCompressed.BAK'
WITH COMPRESSION
GO

-- How does that compare to the uncompressed backup?
SELECT physical_device_name, backup_size, compressed_backup_size
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily
 ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.database_name = 'AdventureWorks2012'
 AND backupmediafamily.physical_device_name 
  IN ('C:\SQLBackups\EncryptedAdventureWorksUncompressed.BAK',
      'C:\SQLBackups\EncryptedAdventureWorksCompressed.BAK')

Which gives us:

transparent data encryption

Uh-oh, wait a sec. What happened? Where’s our 75% compression?

Much like those comic book ads offering us X-Ray vision from a pair of cheap glasses, the truth is in the fine print. In this case, the fine print is found on MSDN in this article: http://msdn.microsoft.com/en-us/library/bb964719(v=sql.100).ASPX

Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

Coming from a world where backup products like Quest’s LiteSpeed or RedGate’s SQL Backup, teamed up with database encryption tools like Encryptionizer, offered us both protection AND space savings, I found (and still do) this to be a HUGE disappointment. I know, I know, encrypted data doesn’t contain the repeating patterns that make for good compression, but if third-party tools FROM DIFFERENT VENDORS can work together to provide both of these features, why can’t the company who owns the database platform and controls the inner workings make it happen?

With databases that long ago crossed the terabyte threshold, I’m really missing my backup compression. So much so that I considered going back to third-party backup tools, until I discovered that EVEN THEY can’t compress backups from a database that is protected by SQL Server’s built-in encryption. We’re just stuck with large backup files.

How Can You Be The Hero?

First, be aware of this limitation and make sure your boss is aware of it. When planning for your upgrade and expected disk space needs, you don’t want to discover that you didn’t allocate enough disk for your database backups.

Second, consider using table and/or index compression within the database itself. This type of compression occurs before encryption, and actually works very well. I personally reduced an encrypted 850GB database down to just under 500GB using table/index compression.

Does this annoy you as much as it does me? What other features of SQL Server, any version, do you think failed to live up to the hype? Sound off in the comments…

transparent data encryption

The post Transparent Data Encryption’s Dirty Little Secret appeared first on RealSQLGuy.

14 Dec 06:43

Tabular model: Not ready for prime time?

by James Serra

While the tabular model in SSAS makes it much easier to build cubes than the multidimensional model, I am not seeing it used as much as I expected.  One reason is if you are a BI developer and have been using the multidimensional model for a while, you tend to stick with the tool that you are most familiar.  You will need a compelling reason to learn a new tool and make the switch, and with the tabular model being a “version 1″ with some missing features, there is little incentive to make the change.

And those missing features can be show stoppers:

  • Partitions are supported, but are processed serially
  • The cube needs to fit in memory, so there is a size limitation, meaning does not support large datasets
  • No cell-level security
  • Many-to-many relationships not built into the model, requires a work around using DAX
  • Does not support custom Drillthrough Actions (but BIDS Helper adds support for actions in a PivotTable in Excel but not in PerformancePoint)
  • Does not support reporting actions or standard actions (URL, HTML, DataSet, RowSet, and other elements)
  • Does not support Scope assignments, work around is to use XMLA
  • Parent-child hierarchies require DAX expressions
  • Not extensible with .NET custom assemblies
  • Does not support Writeback
  • Does not support language translations
  • Does not support named sets
  • Does not support role-playing dimensions
  • Does not support ability to disable visual totals (security)
  • Does not support ragged hierarchies, work around to use HideMemberIf with BIDS Helper
  • Does not have ability to override Default Member
  • Does not support more than one cube in a database
  • Does not support custom rollups (especially useful when aggregating data following a chart of accounts)
  • Does not support linked objects
  • Does not support unary operators
  • Does not support custom formatting of measures
  • Does not support Data Mining
  • Does not support calculated members
  • Does not support cell calculations
  • Does not allow for display folders, work around is BIDSHelper
  • Does not allow for the naming of the All level
  • Supports real-time access using Directquery (multidimensional uses ROLAP), but Directquery has two limitations: won’t support PerformancePoint and Excel (MDX queries are not supported for a model in DirectQuery mode), and does not cache results
  • Not available in SQL Server Standard Edition

It seems most developers are waiting until the tabular model adds these features before using it for new projects.  While you can look at the list of show stoppers and determine that your new project won’t need them now, it is risky to assume you won’t need one of those features later.  Then what?

The tabular model makes sense for proof-of-concepts or small projects (especially if a user has created a model in Power Pivot as you can import that workbook into the tabular model).  It can also be used for simple models requiring the absolute best performance for the user experience.  Right now I can’t recommend it for anything else.  I was really excited about the Tabular model when it came out, but all these limitations make it hard for me to recommend it to clients to use for new projects.  But it is definitely the future for Microsoft and I am expecting to see more features for the tabular model and very little enhancements to the multidimensional model.

More info:

SQL Server 2012: Multidimensional vs tabular

Building Real-World Microsoft BI Dashboards Today

Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

Comparing Tabular and Multidimensional Solutions (SSAS)

SSAS Tabular Models: The Good, the Bad, the Ugly & the Beautiful (comments)

Multidimensional vs Tabular – Making the correct decision

Microsoft SSAS: Should I use Tabular or Multidimensional?

Understanding the difference between SQL Server Analysis Services Multidimensional and Tabular models

BISM Tabular – Proceed with Caution

Updates about Multidimensional vs Tabular #ssas #msbi

Multidimensional or Tabular

14 Dec 06:42

IO Resource Governance in SQL Server 2014

by SQL Server Team

Resource Governor was introduced in SQL Server 2008 to achieve predictable performance in a multi-tenant environment, to support mixed workload patterns, to provide mechanisms and tools to deal with runaway queries, and to enforce resource limits for CPU and memory. It enables customers to implement database consolidation or to configure their own database as a service. Since then, we’ve been incrementally enhancing this feature in major releases to address the top customer requests in this area. In SQL Server 2014, we are excited to add support for IO resource governance.

What is new?

  • Resource Pools now support configuration of minimum and maximum IOPS per volume in addition to CPU/Memory settings enabling more comprehensive resource isolation controls. See syntax details for Create and Alter resource pool with IO support.
  • Ability to configure the maximum outstanding IO per volume (at instance scope). This knob is meant to calibrate the resource governor feature for your storage subsystem.
  • As a note of clarification, by volume we refer to the disk volume as identified by Windows File system APIs
  • We’ve added new columns in the sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_configuration dynamic management views to surface the IO usage and configuration. In addition, we’ve also added a new DMV called sys.dm_resource_governor_resource_pool_volumes that captures IO usage stats across different volumes used by the instance.
  • Two new XEvents (file_write_enqueued, file_read_enqueued) were added to monitor IO requests to the IO Resource Manager queues. These XEvents follow the conventions of the existing IO related events, such as issuing IO and IO completion
  • Last but not least, we’ve added relevant performance counters such as Disk Read IO/sec, Disk Read Bytes/sec, Avg Disk Read IO (ms), Disk Write IO/sec, Disk Write Bytes/sec, Avg Disk Write IO (ms), Disk Read IO Throttled/sec, Disk Write IO Throttled/sec to the SQLServer:Resource Pool Stats.

How to use it?

Let’s take the following scenario as an example of how IO Resource Governance can be used to control the resource usage in a SQL Server instance:

Let’s suppose we are a Database hoster or running a Private cloud for database consolidation and we need to host multiple databases from different tenants (or customers) within a single SQL Server instance to achieve better density and COGS. If one of the tenants is running a very IO intensive workload, this can saturate the IO subsystem, causing performance problems to concurrent workloads that need to perform IO.

The first step would be to create a Resource Pool for each tenant/database and a classifier function that will map the sessions from each tenant to the corresponding Resource Pool. For example, sessions for Customer 1 can be mapped to Resource Pool 1 and sessions for Customer 2 to Resource Pool 2.

If you want to use IO Resource Governance, it is important to set the min or max IOPS setting for every Resource Pool so that the IO requests are redirected to the governance subsystem and minimum reservations can be honored. In the example below, we set the MAX_IOPS_PER_VOLUME to the maximum value for every Pool:

-- Create 2 resource pools & 2 workload groups.
CREATE RESOURCE POOL Customer1Pool;
CREATE RESOURCE POOL Customer2Pool;
GO

CREATE WORKLOAD GROUP Customer1Group USING Customer1Pool;
CREATE WORKLOAD GROUP Customer2Group USING Customer2Pool;
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
IF ORIGINAL_DB_NAME() = 'Customer1DB'
BEGIN
RETURN 'Customer1Group'
END

IF ORIGINAL_DB_NAME() = 'Customer2DB'
BEGIN
RETURN 'Customer2Group'
END

RETURN 'default'
END;
GO

-- Set the classifier function and enable RG.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Set default values for the resource pools so that IO RG is enabled.
ALTER RESOURCE POOL Customer1Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Classifying each workload to a different Resource Pool allows us to configure the resource limits we want to provide for each tenant and also monitor the resource usage generated by their workload. The graph below (Performance Monitor) shows that the workload from Customer 1 is issuing too many IO requests causing a performance drop for Customer 2:

 

In order to protect Customer 2 and guarantee that he gets consistent performance, regardless of the activity from other tenants, we can set the MIN_IOPS_PER_VOLUME setting for the corresponding Resource Pool. From the graph above, it seems that the system can handle around 1300 IOPS, so we decide to reserve 650 IOPS for Customer 2:

ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=650);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

With this configuration, SQL Server will try to throttle the workloads that are running in other Resource Pools, in order to satisfy the 650 IOPS reservation for Customer 2. In the graph below, we can see that the IOPS of the system are now fairly distributed among the tenants and that the performance for Customer 2 is back to normal despite its noisy neighbor:

 

The MIN_IOPS_PER_VOLUME setting will make a reservation for the Resource Pool, but it won’t set any limit for the maximum IOPS it can use. This means that the tenants will still get performance variation depending on how active the rest of the tenants on the instance are. To avoid this problem and guarantee predictable performance, we can set the MAX_IOPS_PER_VOLUME setting for each of the tenants. This will set a hard limit for the maximum IOPS the tenant’s workload can use, guaranteeing predicable performance for the tenant, but also protecting the rest of the tenants on the instance:

ALTER RESOURCE POOL Customer2Pool WITH (MAX_IOPS_PER_VOLUME=750)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

By configuring the IO settings on the Resource Pools we can control the resources we want to provide for each tenant. This allows us to guarantee predictable performance regardless of the activity from other tenants or even provide differentiation in SLA for the Database Service based on the amount of the resources customers sign up to reserve.

Another scenario that many of you might find applicable is isolating your OLTP workload from any maintenance operations that need to run in the database. Rebuilding an index, for example, is a common operation that can trigger a large number of IO requests, as it needs to scan the whole index or table. By using IO Resource Governance we can limit the number of IO operations these tasks can perform and guarantee predictable performance for concurrent OLTP workload.

In this case, we need a Resource Pool dedicated for maintenance operations and a classifier function that will map maintenance sessions to the corresponding Resource Pool. Running these operation as a different user might be a good way to distinguish between regular and maintenance sessions. By configuring the MAX_IOPS_PER_VOLUME setting on the “maintenance” Resource Pool, we can limit the number of IO operations these tasks can perform and protect concurrent OLTP workload from being impacted.

I hope the example scenarios above demonstrate the core value of this feature.

Call to Action

Please try this feature right away by downloading the SQL Server 2014 CTP2. Even more easily test it in on the SQL Server 2014 CTP2 images that are now available in Windows Azure. We look forward to hearing your feedback.

14 Dec 06:41

Recording of SQL Injection Webcast Now Available

On Tuesday I gave a webcast along with MSSQLTips on SQL Injection. If you were unable to attend (or were able to attend and want to see it again), you can view it at the following link [registration required]:

SQL Injection: What it is, how it happens and how to stop it?

I was asked about the slides and scripts. You can find them as a download here:

SQL Injection Presentation Materials

 

14 Dec 05:33

Backup Compression - time for an overhaul

by jchang
Database backup compression is incredibly useful and valuable. This became popular with then Imceda (later Quest and now Dell) LiteSpeed. SQL Server version 2008 added backup compression for Enterprise Edition only. The SQL Server EE native backup feature...(read more)
14 Dec 05:32

IPv6 for the Windows Administrator: How Name Resolution Works in a Dual IPv4/IPv6 Scenario

by Mark Morowczynski [MSFT]

Hello, this is Venkat Kalyanasundaram, Senior PFE from New York Metro area. 

As someone that works a lot with IPv6 I see the same set of questions come up over and over again. I want to take some time to address some of the more common ones and really try to remove the mystery of IPv6. This will be one of a series of posts so check back often for more IPv6 goodness.

The first question I want to address is an extremely common one. How does the name resolution process work in dual stack scenario with Windows? In specific, we would like to know the impact to our network environment when IPv6 is enabled in Windows?

Well let’s just get this out of the way. As we’ve said before we do not recommend disabling IPv6 in Windows (http://technet.microsoft.com/en-US/network/cc987595.aspx, Review the FAQ “What are Microsoft recommendations about disabling IPv6” for more specifics.)

The key here is to get some basic understanding on how the name resolution process works in Windows when both IPv4 and IPv6 are enabled.

In the IPv4 world, we are used to managing the name resolution process using Microsoft WINS and DNS. In IPv6 world, WINS has no relevance. In other words, IPv6 addresses do not get registered in WINS and they need to be managed completely via DNS. IPv6 address is represented as an AAAA (Quad A) record in DNS similar to how we represent IPv4 address as Host A record. The concept of PTR record (for reverse name resolution) also applies to IPv6 addresses in DNS.

With IPv4, normally we are used to just one IP address getting registered in DNS for a given machine. With IPv6 we have to deal with different types of addresses (Link local, Global etc as discussed earlier by Mark and Ray in their blog. http://blogs.technet.com/b/askpfeplat/archive/2013/06/24/ipv6-for-the-windows-administrator-ipv6-fundamentals.aspx). Note all types of IPv6 addresses are not registered in DNS. For example, Link local address starting with FE80: (active only with in its local subnet and not a routable address) do not get registered in DNS. Global address get registered in DNS as Quad A record. When dealing with Transition technologies, Teredo addresses do not get registered in DNS. The Microsoft Technet article DNS Client behavior explains this behavior in detail.

Let us review few real life scenarios where the existing Corp network is pretty much 100% running in IPv4 and you are trying to bring up new Windows machines with dual stack enabled. We will try to find out how these new Windows machines communicate on the existing network.

Scenario #1:

clip_image002

As you can see from the above diagram, we have two machines (say Windows 7 client and Appserver1 running with Windows 2008/R2) located in two different subnets running with both IPv4 and IPv6 enabled (Dual stack). When you enable IPv6, the Link Local address (starting with FE80: that you can verify with IPCONFIG output) will be configured automatically in Windows. We will not go into the detail now on how the Link Local Address get generated automatically based on certain parameters. That is a separate topic for another day. Assume we have not configured any other global IPv6 address or addresses from transitional technologies for IPv6.

Appserver1 registers its IPv4 address in DNS. The Link Local Address from IPv6 do not get registered in DNS since it is a not routable address and confined to the local subnet. In other words, we have only one host A record pointing to 10.1.0.2 for Appserver1 name in DNS even though we had enabled IPv6 in the Server.

Say Windows 7 client needs to communicate to Appserver1, let us see how things work in the background from network and name resolution perspective. Initially the client sends a request to DNS server for resolving the name “Appserver1.Contoso.com”. DNS responds with A record pointing to 10.1.0.2 for Appserver1. Now the client communicates to Appserver1 by sending the request to 10.1.0.2 address. As you can clearly see, the communication still happened via IPv4 even though we had enabled both IPv4 and IPv6 in both client and Server. There is no impact to the network from performance perspective. Assume the client or Server is configured only with IPv4 address (no IPv6 address) we will be encountering the same behavior as explained above.

Scenario #2:

clip_image004

Let us extend the scenario #1 further. Say the Client now is configured with dual stack (IPv4 address and Link local IPv6 address as in scenario # 1) and Server is configured with IPv4 address and a global IPv6 address.

In this scenario, the Appserver1 will register A record (for IPv4 address 10.1.0.2) and AAAA record (for the global IPv6 address) in DNS. Link local address is also generated in this case in AppServer1 which do not get registered in DNS.

What will happen when the Win7 client wants to communicate to Appserver1 in this scenario? The client sends a request to DNS Server only for the A record to resolve the name Appserver1. The client never tries to query for AAAA record (even though DNS has AAAA record for Appserver1) since it knows it is configured with link local address only which is not a routable address. DNS responds back with IPv4 address. Now the client communicates via IPv4 similar to scenario #1.

Scenario #3:

clip_image006

Assume we configure global Ipv6 address in both client and Server in this scenario. Assume your network team is configuring the routers also for proper routing with IPv6 addresses. This means you are taking explicit actions to configure global IPv6 address in your network either manually or from DHCP server or from the Router. (yes, you can configure IPv6 address directly from the router. Covered here briefly http://blogs.technet.com/b/askpfeplat/archive/2013/07/08/ipv6-for-the-windows-administrator-more-ipv6-subnetting-zones-address-autoconfiguration-router-advertisements-and-ipv4-comparisons.aspx )

In this scenario both client and Server registers A record (IPv4 address) and AAAA record (Global IPv6 address) in DNS. When the Win7 client tries to communicate to Appserver1 in this scenario, it communicates to DNS for resolving the name Appserver1. DNS responds back with A (pointing to IPv4 address) and AAAA values (pointing to global IPv6 address) for Appserver1. The client communicates to the server via IPv6 in this case. With both A and AAAA records resolvable, the default behavior in Windows is to try communicating via IPv6.

Quick Recap…

As you can see from above scenarios, the scenario #3 is the only one where the client and server communicate via IPv6.In this scenario explicit actions are taken by Windows and Network administrators to configure global IPv6 address in Windows machines and routers. If you do not take any explicit actions and end up enabling both IPv4 and IPv6 in Windows machines with default settings, the impact is very minimal as you can see from scenario #1 and scenario #2.

As you have noticed in the above scenarios, we had used IPv4 addresses which are in private range. If your network is using public range IPv4 addresses (ranges other than 10.x.y.z, 172.16.x.y, 192.168.x.y) there are few more things that we need to be aware. I will cover this scenario in my next blog post.

Venkat

14 Dec 05:32

Updates about Multidimensional vs Tabular #ssas #msbi

by Marco Russo (SQLBI)

I recently read the blog post from James Serra Tabular model: Not ready for prime time? (read also the comments because there are discussions about a few points raised by James) and the following post from Christian Wade Multidimensional or Tabular. In the last 2 years I worked with many companies adopting Tabular in different scenarios and I agree with some of the points expressed by James in his post (especially about missing features in Tabular if compared to Multidimensional), but I strongly disagree in others.

In general, Tabular is a good choice for a new project when:

  • the development team does not have a good knowledge of Multidimensional and MDX (DAX is faster to learn, not so easy as it is sold by MS, but definitely easier than MDX)
  • you don’t need calculations based on hierarchies (common in certain financial applications, but not so common as it could seem)
  • there are important calculations based on distinct count measures
  • there are complex calculations based on many-to-many relationships

Until now, I never suggested to migrate an existing Multidimensional model to a Tabular one. There should be very important reasons for that, such as performance issues in distinct count and many-to-many relationships that cannot be easily solved by optimizing the Multidimensional model, but I still never encountered this scenario.

I would say that in 80% of the new projects, you might use either Multidimensional or Tabular and the real difference is the time-to-market depending on the skills of the development team. So it’s not strange that who is used to Multidimensional is not moving to Tabular, not getting a particular benefit from the new model unless specific requirements exist. The recent DAXMD feature that allows using SharePoint Power View on Multidimensional is a really important one, even if I’d like having also Excel Power View enabled for this scenario (this should be just a question of time).

Another scenario in which I’m seeing a growing adoption of Tabular is in companies that creates models for their product/service and do that by using XMLA or Tabular AMO 2012. I am used to call them ISVs, even if those providing services cannot be really defined in this way. These companies are facing the multitenancy challenge with Tabular and even if this is a niche market, I see some potential here, because adopting Tabular seems a much more natural choice than Multidimensional in those scenario where an analytical engine has to be embedded to deliver one of the features of a larger product/service delivered to customers.

I’d like to see other feedbacks in the comments: tell your story of choosing between Tabular and Multidimensional in a BI project you started with SQL Server 2012, thanks!

14 Dec 05:32

New In-Memory OLTP Sample for SQL Server 2014 CTP2

by SQL Server Team

To help you get started with the new In-Memory OLTP feature, we created a sample around sales order processing based on the AdventureWorks sample database.

The sample, along with installation and usage instructions, are available on Codeplex:

https://msftdbprodsamples.codeplex.com/releases/view/114491

We encourage you to download and install the sample to become familiar with the new memory-optimized tables and natively compiled stored procedures, introduced by the In-Memory OLTP feature in SQL Server 2014 CTP2.

We also included a number of demo stored procedures in the sample, which can be used to measure the performance of the sample on your system, and to contrast the performance of the new memory-optimized tables with traditional disk-based tables. Instructions for running the demo stored procedures, and for testing at scale, are in the documentation included with the sample.

The sample is a work in progress, and we appreciate any and all feedback. You can post feedback and questions on the SQL Server Samples Forum.

We will be updating the sample regularly based on your feedback, as we get closer to the RTM release.

14 Dec 05:32

Automated Permissions Auditing With Powershell and T-SQL: Part 1

by Tracy McKibben
rubbergloveshouse

A big part of my job is controlling security to multiple production databases, and reporting on that security for quarterly audits.  In my environment, it’s not important to know who has access to specific tables or other objects, but we are interested in knowing who has some form of read or write access to a database, or who might be a DBO or sysadmin.

This was largely a manual process, until I decided to dive into Powershell.  Once I began to realize the power of Powershell (pun intended), I began to scheme ways that it could help provide better visibility into our database security.

After a few iterations, I finally settled on a combination of Powershell and T-SQL.  It’s not terribly complicated, but the overall script is quite large, so I’ll present it here in functional parts, and make the overall script available.

Without further ado, let’s begin…

The first functional piece of the audit script that I’d like to present is the T-SQL query used to collect login details from my SQL 2005 and SQL 2008 servers.  A different query is used for SQL 2000, which I’ll present later.  The Powershell script determines the version of a particular SQL Server instance and decides which query to run – I’ll present this part later as well.

This query returns every login from a SQL 2005/2008 database, including bits to indicate that the login has read access, write access, DBO access, or sysadmin access.

SELECT
   ServerName          = @@SERVERNAME,
   LoginName           = AccessSummary.LoginName,
   LoginType           = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
   DatabaseName        = DB_NAME(),
   SelectAccess        = MAX(AccessSummary.SelectAccess),
   InsertAccess        = MAX(AccessSummary.InsertAccess),
   UpdateAccess        = MAX(AccessSummary.UpdateAccess),
   DeleteAccess        = MAX(AccessSummary.DeleteAccess),
   DBOAccess           = MAX(AccessSummary.DBOAccess),
   SysadminAccess      = MAX(AccessSummary.SysadminAccess)
FROM
   (
       /* Get logins with permissions */
       SELECT 
           LoginName           = sysDatabasePrincipal.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipal
           ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get group members with permissions */
       SELECT 
           LoginName           = sysDatabasePrincipalMember.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
           ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'
           AND sysDatabasePrincipalRole.name <> 'public'
           AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in db_owner, db_datareader and db_datawriter */
       SELECT
           LoginName           = sysServerPrincipal.name,
           SelectAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DBOAccess           = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,
           SysadminAccess      = 0
       FROM sys.database_principals AS sysDatabasePrincipalRole
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')
           AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in sysadmin */
       SELECT
           LoginName           = sysServerPrincipalMember.name,
           SelectAccess        = 1,
           InsertAccess        = 1,
           UpdateAccess        = 1,
           DeleteAccess        = 1,
           DBOAccess           = 0,
           SysadminAccess      = 1
       FROM sys.server_principals AS sysServerPrincipalRole
       INNER JOIN sys.server_role_members AS sysServerRoleMember
           ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipalMember
           ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
       WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipalMember.is_disabled = 0
   ) AS AccessSummary
INNER JOIN MASTER.dbo.syslogins AS syslogins
   ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
GROUP BY
   AccessSummary.LoginName,
   CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END

The post Automated Permissions Auditing With Powershell and T-SQL: Part 1 appeared first on RealSQLGuy.

14 Dec 05:18

Power BI first impressions

by James Serra

I have been using Power BI the past few weeks (see my video of all the features) after I was invited by Microsoft to test the Power BI for Office 365 Preview (register here).  I have to say I am extremely impressed.  It allows for a self-service reporting and ETL environment that can be a game-changer.  Power BI is an umbrella name for many technologies and tools that blend well together.  Excel is the “center of the universe” for all these tools, and works in combination with SharePoint Online.

Note that Office 365 is really just a different way of paying for Office 2013 (subscription based vs one-time fee), but you are allowed additional installations and get additional services (see comparison, Office 2013 or Office 365: What’s the Difference and Why So Confusing?, and What’s the Difference Between Office 365 and Office 2013?).

Microsoft describes it as: Power BI for Office 365 is a complete self-service Business Intelligence (BI) solution delivered through Excel and Office 365 providing you with data discovery, analysis, and visualization capabilities to identify deeper business insights from your data. The Power BI for Office 365 service is a cloud-based solution that reduces the barriers to deploying a business intelligence environment for sharing reports and accessing information.

The people who this benefits:

  • Report creator/user - such as a data analyst, data or BI consultant, or a mainstream Excel user – you’ll likely use Power BI’s self-service BI features in Excel often, and extensively.  Online services that support collaboration, such as distributing interactive reports and sharing workbooks, are also an important central workspace and information hub
  • Data stewards and IT professionals – such as a data scientist, a data administrator, or an IT professional – the online service features of Power BI enable you to provide specific and secure access to data resources.  Online service-related elements of certain self-service BI tools – such as enabling the identification, selection, and secure distribution of on-premise and public data feeds – are also significant.  Users can share and manage the data and queries they create in Excel through the Power BI for Office 365 service, and also track usage
  • Report consumers – anyone from an aspiring students to a CEO – you can collaborate, share securely, and interact with reports using Power BI for smarter, insightful, and more nimble decisions.  This includes using an online hub, built right into Office 365, specifically tailored for collaborative BI

In short, Power BI is an environment power business reporting in the cloud.  It lets you easily extract and transform data from both on-premises and cloud sources using Power Query.  Then, you can shape your data model, enrich it with business logic using Power Pivot, and create gorgeous reports with both Power Map and Power View.  Power BI sites allows you to share reports and data views via SharePoint Online, and Power BI Mobile allows those same reports to be seen on a mobile device.  Data on-premises can be easily refreshed in the cloud thanks to the Data Management Gateway.  Finally, Power BI Q&A allows you to query for data by entering a question and seeing the answer instantaneously.

Here is an example of how a business user would use these tools to generate a reporting solution:

Power Query: (download).  Still in beta.  Helps customers easily discover, combine and transform their data.  You can do an online search and import from many different public data sources such as data.gov, Windows Azure Marketplace (14 free sources), and Wikipedia (listed here), as well as import from many external data sources (listed here).  These external data sources include databases (SQL Server, Azure, etc), any website by entering a URL (it will automatically locate the tables), files (Excel, csv, etc), and other sources (SharePoint list, OData feed, Hadoop, etc).  You can share any query, where the following information (metadata) about the shared query automatically gets stored in the Power BI Data Catalog in the cloud (hosted in SharePoint Online):

  • Query name and description
  • Column names
  • Location of the data sources used by the query.  For example, URL, server name/database name, and computer name/file name depending upon the type of data source.  Once Power BI is aware of a data source and stores it in the Power BI Data Catalog, you can view and manage the data sources using the Manage Data portal to annotate them with friendly names, descriptions, and access URLs.  For more information, see Manage Data Source Information using the Manage Data Portal
  • Search access list for the query to determine which users/security groups can find and use this shared query
  • The URL for query documentation, if specified
  • A preview of the data referenced by the query, if data preview was selected for the query.
  • The query definition in the form of a Power Query script that specifies how to connect to the query’s data sources and transform the data. This script is created when you filter and shape data in your query

This information in the Power BI Data Catalog helps users in finding and using the shared queries.  When you use a shared query in Power Query, the query definition is executed in Excel to import the data referenced by the query into a new worksheet in Excel.  To find and consume shared queries using Online Search in Power Query, see Find and Use Shared Queries.  Note that when you click the “Online Search” button on the Power Query tab, another tab is made available called “Search” that has a lot of other features for scoping and filtering your query search.

You can also optionally index the data the query retrieves and schedule when the index runs [indexing the data not supported yet].

USER EXAMPLE: Open Excel, go to the Power Query tab and choose “Online search”.  Pull in “S&P 500 Component Stocks” from Wikipedia.  Assuming you have a local csv file with stock data (get it here), choose From File -> From CSV.  You can filter and shape the data as needed (add/remove columns, remove duplicates, change types, etc – see Guide to Power Query Context Menus).  When adding a column there is a powerful Power Query Formula Language, informally known as “M”, that you can use.  You can create your own queries that use M – see Create an advanced query.  Rename the query to something like “Daily S&P 500 data of 10/15/2013″.  Then you mash the two tables together via the Merge button (selecting the box “Only include matching rows”), using the Ticker fields, making sure to select all the fields in the second table.  Rename the query “S&P 500 stocks and daily data MERGE” and hit the “Load to data model”.

Power Pivot: Included with Excel 2013, you just need to enable it.  Power Pivot allows you to work with very large data sets and do things such as create calculations, aggregate sums, create relationships, and create hierarchies.

USER EXAMPLE: Go to the PowerPivot tab and choose “Manage”.  Add a hierarchy that includes “GICS Sector” and “GICS Sub Industry”.  Add a column “$ Day Change” that subtracts the close price from the open price and a “% Day Change” that divides the open price from the close price.  Hide from client tools the column “SEC filings”.  AutoSum “$ Day Change”.

Power View: Included with Excel 2013, you just need to enable it.  Power view is interactive data visualization experience that allows you to build very cool reports.  Data within an Excel sheet can be used as a data source (in which Excel creates a linked table in the Excel Data Model behind the scenes automatically or you can manually do it via the “Add to Data Model” button).  Optionally this data model can be edited in Power Pivot to create a more sophisticated data model.

USER EXAMPLE: Go to the Insert tab in Excel and choose Power View.  It will use the query you loaded into the data model to build a default report.  Drag fields onto the report and go to the Design tab to change the type of chart.  Use the Layout tab to show the data labels.  Demo a slicer by using GICS Sub Industry as the chart and GICS Sector as the slicer.  Demo the Map by using Address of Headquarters as the chart.

Power Map: (download).  Still in beta.  Gives users the ability to plot geographic and temporal data visually on Bing Maps, analyze that data in 3D, and create interactive tours to share with others.  These videos can be shared anywhere, including social media, PowerPoint slides, and Office 365.

USER EXAMPLE: Download the Power Map Sample Workbooks and play with them via the tab Insert -> Map -> Launch Power Map and hitting “Play Tour”.

Power BI Sites: Still in beta.  View the Power BI Provisioning Video and the provisioning guide. The Power BI site supersedes Power Gallery. It’s the hosting site for deploying Power Pivot models for up to 250 MB in size, allowing teams to share reports and data views.   Power BI sites are implemented as an app for Sharepoint; there’s a detailed explanation of what they are and how they work here.  Also not how they are different from the BI Center Site in Office 365: Compare a BI Center site to Power BI for Office 365 sites.

USER EXAMPLE: Go to your Power BI site and choose “add” for documents to upload a workbook you just created.  Click on the “Shared With” for the workbook to specify who can view/edit it and send them an email.  Uses can edit the workbook in Excel on their desktop or via Excel Web App in their browser.  Note: Power BI does not work in IE9 or older browsers.

Data Management:  There is a “Manage Data” link on the Power BI site that shows usage analytics for all shared queries, a list of the queries you shared, and the data sources used by those shared queries.

Power BI Admin Center: There is a client agent called the Data Management Gateway (download) that enables cloud access for on-premises data sources on your server and exposes data from these on-premises data sources as OData feeds for corporate-wide access via Power Query.

To do this, you download and install the client agent on your server (install instructions) and enter a gateway key (obtained on the Admin Center by creating a gateway) to register the gateway with the Admin Center (note it currently only supports SQL Server and SSIS packages as data sources.  In the meantime, as a workaround you can use linked servers or SSIS Complex Data Feeds to expose other sources).  Once it is installed, it establishes a communication channel with your Power BI service in the cloud (no firewall holes required), and essentially waits for requests.  Once it gets one, it acts as a broker between your cloud service and your on premises data.

You then create a new data source in the Admin Center (selecting the gateway and providing the connection string) which will enable data from the on-premise data source as a OData feed that can be accessed by users through Power Query.  When creating the data source you choose which tables and views are included as well as which users/groups are allowed to search for the OData feed in Power Query.  If your table/view contains any unsupported types, or does not have a primary key, or does not have at least one non-nullable column, the entire table/view will be unavailable for publishing (it will be greyed out).  A OData feed URL is created when the new data source is created, and that URL is specified in Excel when choosing to connect to a OData feed (but the URL is not needed if using Power Query Online Search).  See Create a Data Source and Enable OData Feed in the Power BI Admin Center.

As you can observe from the URL, this is a cloud service.  You should be able to connect to the service from anywhere, and it will connect through the gateway to serve up the data.  While this is great from a mobility standpoint, if you happen to be on premises, this would be quite inefficient, as the data would first need to be transferred to the endpoint in the cloud, and then back to the source network.

The good news is that the gateway is able to detect when you are accessing the feed locally, and it will redirect you to the source without sending the data up to the cloud and back.  The bad news for us preview users is that this is the only thing working at the moment.  Therefore, for the preview period at least, in order to access the OData feed, you must be on a local network.  Specifically, you must be able to resolve the server name defined in the connection string.

Metadata for the OData feed is automatically indexed in the Data Catalog in the cloud, so users don’t need to know the server name, connection info, etc.  You can also optionally index the data for the OData feed and schedule it [data indexes not supported yet].  See Index OData Feeds.

Users can then search in Power Query for the OData feed by using the keywords from the following:

  • Name of the data source in the Admin Center
  • Description of the data source in the Admin Center
  • Names of tables
  • Names of columns in tables

In the Admin Center you can also create a data source that allows Excel workbooks in SharePoint Online with a Power Point model to refresh from a on-premise data source.  Do this via the “create cloud accessible source” task.  When creating the data source you will select the gateway and provide the connection string as well as specifying which users/groups are allowed to access this data source.  There are some limitations in this preview version: Data refresh scheduling is as yet unavailable (it must be done manually), and the data sources that can be refreshed are restricted to direct SQL connections.  Also, models created with Power Query cannot yet be refreshed (Power Pivot created connections and public OData feeds only).

When a refresh is requested, the model is interrogated for its data connections.  The data catalog is then interrogated for a data source with a matching connection string, and if found, is used.  The Gateway is then called to retrieve the data if it is on premises.  If the data source is SQL Azure, the Gateway is still used, but the data is loaded directly from SQL Azure – it does not need to be sent to the Gateway first.

Finally, there is a page in Power BI to view the System health (CPU, gateway availability, logs, indexing).

This is demonstrated well at Power BI – Working With the Data Management Gateway.

Power BI for mobile: (download).  Mobile BI access to reports for Office 365 is provided through new HTML 5 support and a native mobile application for Windows 8 tablets.  So this only works for Windows 8/RT devices, but iPhone and Android support is expected soon.

Power BI Q&A: Takes enterprise data search and exploration to a whole new level.  The search experience is instantaneous and uses natural language query – Q&A interprets the question the user is asking and serves up the correct interactive chart or graph.  This only works with the sample data (Olympics, Retail Bar Sales) that is installed when you create the Power BI sites, but it is amazing.

Should you use the 32-bit or 64-bit version of Excel?  Always use 64-bit!  Here is why:

Excel description:

32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.

64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.

The maximum worksheet size is 1,048,576 rows.

NOTE: When I followed the examples above of using all the tools in 32-bit Excel, I ran into memory constraints that caused Excel to freeze or error-out.  When I used the 64-bit version, I did not experience any of those problems.

Power Pivot description:

PowerPivot supports files up to 2GB in size (after compression).  This is an “artificial” restriction that allows it to be uploaded into SharePoint.  The 64-bit version of PowerPivot enables you to work with up to 4GB of data in memory, and the 32-bit version enables you to work with up to 2GB of data in memory (but realistically only 500-700MB, see How much data can I load into PowerPivot?).  And because of compression, PowerPivot can fit an additional 1-10x times the size of the database into memory, so the 64-bit version can fit a 4GB-40GB database into memory.  Typical compression runs about 2:1.

Number of rows in a table is 1,999,999,997.

More info:

Power BI Preview Review

Getting Started with Power BI for Office 365 preview

Power BI blog

Power BI forum

Power BI – Why would you ever need Power Query when I already have Power Pivot?

Microsoft Updates Power BI for Office 365 Preview with New Natural Language Search, Mapping Capabilities

What Problems does Power BI solve?

What’s New in the Power Query October 2013 Update

Is Microsoft Power BI a Game Changer?

Power BI – Getting Started Guide

05 Dec 18:27

Virtualizing Exchange and Scaling Up

by Matt Liebowitz
EMC logo

I have to admit - Exchange virtualization is one of my favorite topics.  I love talking about it with customers, colleagues, or anyone who will listen. It has a very well known workload profile which makes sizing consistent, it’s virtualization friendly in that it supports vSphere features like vMotion and HA, and I like talking about the challenges and creative solutions that are sometimes required to virtualize it. Yep, Exchange virtualization is my pal.

I’m also very familiar with its support limitations as I’ve written about them in the past (see here). One of the support policies has to do with CPU over-subscription. That is, assigning more vCPUs than there are physical pCPUs in the host. From Microsoft’s support policy page for Exchange 2013 virtualization (though this applies to Exchange 2010 also):

Many hardware virtualization products allow you to specify the number of virtual processors that should be allocated to each guest virtual machine. The virtual processors located in the guest virtual machine share a fixed number of logical processors in the physical system. Exchange supports a virtual processor-to-logical processor ratio no greater than 2:1, although we recommend a ratio of 1:1. For example, a dual processor system using quad core processors contains a total of 8 logical processors in the host system. On a system with this configuration, don't allocate more than a total of 16 virtual processors to all guest virtual machines combined.

Note the section I bolded above – that says that you cannot go beyond a 2:1 vCPU to pCPU ratio and 1:1 is recommended. Not only are we used to much larger consolidation ratios, once you factor in DRS automatically moving VMs to balance out utilization it becomes almost impossible to enforce.

Combine this with the relative disparity between the maximum amount of RAM in a host compared to CPUs. We have customers that have deployed ESXi hosts that have 16-24 physical CPUs but 1TB of RAM. If you want to virtualize Exchange on those servers, you’re left with only two possible outcomes:

1) You ignore Microsoft’s support restriction and exceed the 2:1 vCPU/pCPU ratio.

2) You don’t use anywhere near the amount of RAM you have in the host.

The good news is there’s a third option that allows you to work around this problem: CPU reservations. CPU reservations in vSphere allow you to guarantee that important virtual machines will have access to the CPU resources they need even if CPU resources are constrained. This works great for virtualizing Exchange for a few reasons:

1) You can effectively exceed the 2:1 vCPU:pCPU ratio for workloads other than Exchange while guaranteeing Exchange VMs access to those CPU resources. Doing so does not violate Microsoft’s support policies – see this great TechEd presentation on virtualizing Exchange where Microsoft’s Jeff Mealiffe discusses this as an option.

2) Hosts that have a disproportionate amount of RAM to CPUs can still be utilized without wasting resources.

The other nice thing about CPU reservations is that when resources are not over-subscribed, CPU resources can be shared evenly with VMs that do not have a CPU reservation. In this way they work differently than memory reservations, which do not allow reserved memory resources to be shared with other VMs even if memory is plentiful. With CPU reservations, when resources are available then all VMs get equal access to the CPU. Once CPU resources become over-subscribed, the VMs with a reservation get guaranteed access to their reserved CPU resources while VMs without CPU reservations do not.

It’s worth mentioning that this post came about mostly as a result of a Twitter conversation that Chris Wahl (@ChrisWahl) brought me into recently. This is one of the reasons why I love Twitter – great conversation that led to sharing of information.

I hope this helps those that are looking to virtualize Exchange and are concerned about the CPU over-subscription limitations. Using CPU reservations in vSphere will let you stay compliant with Exchange support while allowing you to over-subscribe CPU resources for non-Exchange workloads.  Everybody wins!

01 Nov 06:36

The Call of WTF

by Lorne Kates

From the archived blog of Paul, recovered from a USB stick found beneath the raised tiles of a decommissioned server room, long forgotten.

The most merciful thing in the world, I think, is the inability of the human mind to correlate all its contents. But what of these modern times of connectivity, with the ease of the piecing together of disassociated knowledge? Can any information be safely sequestered away-- fragmented and separated, never to come together and burrow in the minds of men?

My knowledge of such things began in the time of yore, almost time immemorial, during my tenure at Initech. It is where I discovered the madness that lurks in my mind, my soul, my dreams, even to this day. A madness I pray can be excised with this blog, that through writing these words I may achieve some form of catharsis and perhaps, at last, peace.

I. The Horror in ASCII

I was once a technician at Initech, at first a lowly one, but then increasingly senior as the company saw greater and greater success. The company grew larger, its tendrils hungry to feed off more. It drew in more resources. More people, each more diverse than the last, in both personality and physical location. Entire companies were joined to Initech by way of merger and acquisition. Initech's infrastructure grew in lockstep. Servers were purchased. Remote locations came online. And then the catalyst of this fateful tale; an alliance with IBM.

In an amount of time far swifter than a humble technician like me was ever used to, a wave of blue change swept through Initech. The details are boring and beneath us both, dear reader. But sufficient to say, everything became IBM. Initiech's ERP was changed, and it now ran on AIX, the blue giant's own flavor of UNIX.

With the changes done, Initech turned its new infrastructure and capabilities on Minitech, a large competitor, and swooped upon it to perform the inevitable and unavoidable acquisition.

But while the leaders and scholars of Initech were looking out, technicians such as I were looking in.

In those tumultuous days, with so many of our people being remote, but all our resources being centralized, remote consoles were the only way to bring both together. Employees connected to the ERP software via Telnet, using the Korn shell. Occasionally-- well, far more often than that word would imply-- something would go haywire with the dial-up connection. The modem would misbehave, the phone line would be too noisy, or perhaps just fate would intervene in its mysterious ways. The connection would be assaulted with electrical noise, spraying the current working directory with an acidic wash of random ASCII. By methods and designs beyond my ken, those maelstroms of incomprehensibility would wander too close to a cat command, or end up on the wrong side of a pipe. Those unfortunate directories would be overwhelmed with bizarre files with unpronounceable names. The resultant directory listings would be littered with these mangled corpses of false files, a visual mess beyond visual understanding.

It was on the eve of the acquisition of Minitech when I looked too far inwards with only the best of intentions. But intentions are only as good as their execution. Results of actions are the reality of the world, as are all the consequences wrought by them.

I, with my aforementioned intentions, wanted to welcome our new Minitech brethren with a system that was not littered with these abominations that lay foul to our ERP. These malformed files had always been a horror I tried to see only in my periphery. Something I acknowledged existed, but did everything in my conscious power to avoid interactions with. But I was interacting with the system during the merging, and these things were ubiquitous. Pieces of madness, strewn about. Little things, like motes in the corner of your eye. Tiny, unnoticed. Until they're notice. Then you cannot unsee them. I was driven.

But I did not know how to excise these devils. How can you drive away that which you cannot even address directly?

So in my thrice-damned cleverness, I devised a workaround. One that could only be borne somewhere on the continuum of youthful bravado, and outright insanity. Before this incident, I could excuse my behavior as the former. Afterwards, I can only dare to hope to escape from the latter which I know pulls at my hindbrain even to this day.

I could not refer to those modem-noise files directly by name. But, oh wonders of scripting, I could enumerate the extant of respectably-named files in a directory. With arrogant keystrokes, I created a script that create a temporary folder elsewhere, tuck away all the proper files into the encompassing safety of its embrace-- and then in a single fell swoop, banish the malformed miscreants into the ether with a single rm * command.

I ran my precious, foolish script on a directory or two, and it worked a wonder. The directories at long last were pure and clean, swept free like a warm, sweet wind lifting the heavy lung-scalding smog from a valley. It worked well and I congratulated myself. But-- oh, hubris, the folly of man-- I knew cleaning a few personal directories would not be enough. There was an entire filesystem that, over the many epochs of time, had been defiled upon by the foulness.

It was everywhere, and at last I could fix it. I unleashed my script like the furious right hand of the archangels themselves. Directory after directory, expunged. And it worked perfectly everywhere.

Everywhere, that is, until I discovered modem-noise files in the root directory.

In a hazy stupor, like being drunk on the power I wielded, I turned my script on the root directory, and unleashed. It would be simple enough, just like every other cleansing thus far. Move the good stuff into a temporary folder. Remove the noise. Move the good stuff back.

I never made it past the first step. Perhaps a small part of my brain realized only after the command had been executed what the connotations of what I'd done were. Surely, dear reader, as an experienced Unix system admin, you can feel the horror that only dawned on me like a rising tide. My terminal froze, locked up with a sudden, bone-jarring stop.

A chilling realization emerged in me, and become clearer with each flash of the pulsating, yet now lifeless, cursor.

The server must have been using those files.

In the glow of only the infernal, useless terminal, and the midnight moon streaming into the office-- I nearly wept.

That this could happen was impossible! It must have been a simple glitch; not unlike those line hiccups that had caused these demon files in the first place. But my terminal did not reboot. I flipped the reset switch. Once. Twice to be sure. And then with a violent outburst, thrice and be damned to hell you infernal server! I was overcome with near mortal anguish, the visions of thousands of users lost adrift, disconnected for eternity, all caused by me. Surely there were backups. Someone could undo this conflagration. Someone could fix it for me right away, first thing in the morning, and be a conquering and well rewarded hero.

But no. None of my fevered ramblings would come to pass. The server was dead, killed by my own folly.

There was naught to be done but travel back to my residence, and spent the night in the sleepless fits that a man wrought with guilt should suffer. And suffer I did. Never had six hours stretched themselves into an eternity with no horizon before my eyes.

II. The Tale of Marie, System Controller

Without knowing the warm comfort of sleep, I returned to the office at a time early enough to know that I'd be the first one in. A shamble of a man, lumbering back to the scene of his horrors. I should be at the front door, personally meeting the face of each employee whose day I had destroyed. Would I greet them apologetic, a meek smile on my face to mask my dread? Prostate myself on the sidewalk before them, the concrete as cracked and trod upon as I felt?

A small blessing in disguise awaited me in the breakroom. Marie, the project manager and system controller, was by the coffee machine, slowly savoring her morning libation. It meant she hadn't yet discovered the fatal deviation I had thrust upon the server. If she had, she would not be enjoying herself as such. A small blessing indeed, for it meant I could brace her.

But I couldn't brace her enough. It was only by the grace of god that she had finished her coffee, so there was nothing left to spill. Without a word, we marched into her office.

She tried to log into the machine from her terminal, a vain attempt for sure, but I could not blame her for wanting to verify my folly herself. It was something almost beyond comprehension. It could only be experienced.

Marie was silent for a moment. Each time her nostrils flared, or her lips parted for breath, I recoiled, knowing a vicious but well deserved berating will burst forth from her. But bless her for being a rock of professionalism amongst the insanity swirling around this situation, for it never came. Instead she opened a drawer that has not been opened in time immemorial, and retrieved a key unlike any I had ever seen. It was oddly shaped; curved around the head but not round; tiny spikes and spokes protruded from it at odd angles. Even though the key was ancient, it was not tarnished. In fact it looked as new as the day it emerged from its forge, it's chrome plating unscathed by use.

"It's a key to the server itself," Marie told me. "Only to be used in the direst of emergencies. A key to the maintenance shell of the server."

Together, we traveled to the server room. We passed the steady stream of arriving employees-- their faces an even mix of blessed innocence, having not approached their terminals yet; and confused, lost and angry from those who had. We went through a door I'd never been though, and down a flight of stairs I barely knew existed. The temperature had noticeably dropped. In the dimly lit concrete hallway that ran the length of the building, but far underneath it, I could almost see my breath escaping from my mouth like a vagrant soul trying to rend its way from this place.

"This leads to the server room," she told me. Which was odd, because I thought the servers were housed upstairs in the tech room. But no, for I was mistaken.

This was the server room specified and equipped by IBM. This was the AIX server room. It was where the ERP itself resided. Separate from the email servers, from the webservers-- from all of the infrastructure I had ever physically touched. This was beyond what I, as a lowly technician, had ever known or dreamed of.

And then we passed beyond a steel door that might once have been red, and entered the server room. Racks I did not recognize. Boxes that looked like nothing I had ever laid eyes upon. Reams of wiring dipped and swooped, to and fro, in and out from places obscured by shadow. None of them were the same. RJ45. Ethernet. Was that a token ring terminator? How ancient were these connections? They seemed to twirl in both directions at once, and skew away at angles that simply did not look right. The geometry of the cable runs was abnormal.

And beyond all this bewildering whorl of technology, a single box sat on a desk like a steel dais, enclosed with a cage of criss-crossed metal to keep the box safe. Or perhaps to keep people safe from the box.

Marie unlatched the cage and parted its double doors slowly, opening them like some unholy ark. The hinges cried out in protest, an unearthly screech extenuated by the deathly silence that was pervasive in the room. No, I suddenly realized-- not silence. A constant and perpetual hum. A single tone at a frequency that would not stay still. An eternal white noise that lurked acoustically in the air. A sound I hadn't noticed, but now couldn't get out of my ears.

Marie inserted the strange key into a receptacle-- a black hole on a black surface in a dark room. It shouldn't be possible to see it. But the key nestled into place. She turned it, and a new sound filled the air. A high pitched sound, and it was rising. Something coming to life, if life is how you would dare to describe it-- this shambling thing that was merely a hollow shell of what it once was.

The built in monitor glowed. The server pulled a maintenance shell from beyond comprehension, from beyond userspace, and projected it onto the screen. Marie laid her fingers on the keyboard.

It was a direct connection to the soul of the entire system.

"Will this work?" I dared to ask her.

"I hope so," she said quietly, her fingertips twitching, "Or else I'll have to call..."

Her voice trailed off.

She typed a command, and the system told her it couldn't be found.

"That-- that cannot be," Marie said, staring at the incomprehensible and impossible error message. That command she'd typed shouldn't be missing. It was a standard command, known by all, repeated by all, in the kernel since the earliest recorded epoch. Maybe even before. It was part of the system.

The system I had sliced into pieces and scattered. Oh.

I spoke of the temporary directory. "Look there for the commands."

"I can't," she replied, "ls is not found."

The sun didn't rise today. The trees have no trunks. Water itself is not wet. To utter any such phrase would have been equal in absurdity to what had just passed Marie's lips.

It can't simply be gone. The ls command must exist. Unless-- no, I couldn't think of it. I refused to acknowledge the thought that gnawed at me. To think of what I'd done. To think of why I'd done it. How I'd done it.

I couldn't address those modem files directly-- only indirectly by inference. Why could it not be that way again?

"Does cp exist?" I asked.

She typed so slowly, fearfully. Tap. Tap. Thunk.

Silence, and then: "Yes."

A single word, but a rush of hope beyond comprehension. As deep as we were in the mouth of madness, a single, solitary word of hope had been uttered, and it was louder than any cacophony of cooling fans.

"We can do this," I said at last, "The commands aren't missing. They just are relocated beyond the veil of the normal directory structure. But I know where they are. In limbo. But we can pluck them back, put them where they belong. Try system commands, one at a time, and if they fail-- we can restore them one at a time with cp."

"I hope so," Marie said again, and typed. The first command copied. Then the second. And a third.

One by one, we identified the commands, pulled them from the temporary void, and restored them. After an hour, we had pulled the server back from the brink of death, and restored a functional operating system. Barely functional. Perhaps I should only describe it as, at best, stabilized. The commands worked, but not as expected. Interactions were off. Permissions were not correct.

And that sliver of hope, just like that, was extinguished under a burden of realization. We had restored the command structure of the server. But there were still files in the void of the temporary directory. And unknown portion of the server was still sequestered away, and the maintenance shell simply could not put them back into place properly. Even if either of us knew how.

"It's over," I said, defeated. But for some reason, Marie wasn't.

"No, it isn't," she said, a wicked grin on her manic face. How could she be pleased? Had the overwhelming burden of what I had befallen upon us broken her at last? "The server's operational again. If you hadn't helped me restore that little bit, then they would have just come in and destroyed it, replaced it wholesale-- along with anything we'd customized or that hadn't been backed up. The entire merger would have been lost. But now-- they can work with this. I know they can."

"They?" I said, confused as Marie pushed past me, striding like a maniac towards a desk in the corner. "Who are they?"

She picked up a dusty wired phone, and phoned the number pasted on the wall. She turned and finally answered me. "IBM Support."

III. The Madness From The Blue

She spoke into the phone, at first in English, but then slowly slipping into tongues I did not comprehend. Acronyms. Chains of words that should be familiar but lost their meaning when put in the order she did. I could not understand Marie, but I could read her body language. Apprehensive and pensive hope.

I wish I could share any of those feelings, but this place and its strangeness was getting to me. Blinking lights. Dark shadows. Machines that may or may not be doing their job.

The central heart and soul of it all limping along the fine line between life and death.

But we had to wait there for those she had summoned. We barely spoke at all. What did we have to talk about? What did we dare say aloud in this place?

Soon enough, thank any deity who would hear me, they arrived. Men in gray suits, decorated with Initech's very-ist of Very Important Person visitor tags-- and IBM identification cards. The ancient handlers arrived, those who brought their tomes and their incantations and rituals. And their price. It would be steep, and non-negotiable.

Marie agreed to it.

They set up around the server, briefcases full of keys like Marie's but stranger. Wires with ends like gaping maws of no shape I had ever seen. Terminals that were like Initech's own, but only on the surface. And before they began, they shooed us out of the server room, like a cult that held onto their secrets tight against the threat of public revelation.

I did not wait for them to ask twice before fleeing that place. Past the not-red door, back up the concrete hallway, up the stairwell, past the hordes of displaced workers, and finally out into the sunlight.

I do not know what went on in that basement, and for the sake of whatever sanity I have left to this day, do not want to know. File systems got mounted. Network connections were massaged back together and brought online. The system was dragged back from the brink, piece by piece, into a patchwork. By the end of the day, terminals flared back to some imitation of life.

I never saw the strange men from IBM leave, but by the time I dared to go back into the office, they were gone.

The server was up once again, accepting terminal connections. It reached out to the world, and let the world reach into it. It was a server once more. All of its myriad pieces had been stitched back together, the chaos of its file system oozed back into place. On the surface, the system looked normal. But I had seen into the deep. Even now, I could see the patchwork. The scars. The artifacts of what had risen. System files with creation dates far too new. Permissions for critical files that weren't set right. Customizations that had simply vanished into the abyss.

Whatever had been done to it was unnatural and just a kind layer of "good enough" overtop a horrific layer of madness and wrongness that went all the way down to its kernel. A constant reminder of that it had once been an empty shell of a system, barely alive-- a portal straight into chaos.

Though the system ostensibly worked, eternal and irrevocable evidence of the madness remained. I could never be confident that some random glitch was not related to the great awakening of chaos, and the desperate efforts of the silent few who put it back to its slumber.

Initech moved on from the incident, and brought Minitech into the fold, and several other companies since. Management always talks of replacing the ERP system, and the AIX server that hosts it. The first few times such a decision was raised, so too were my hopes. My hope that the shambling patchwork beast in the basement could be decommissioned forever-- scrubbed from thought and memory. But that project, like all corporate projects, was always six months away from completion. So while a switchover may occur one day, for now the server was just let to be.

No one delved into details of the incident. Management had a vague idea that something had required a high-level support call. Technicians knew they needed to let well enough be and not touch the patchwork server. One by one they vanished from the company, always with an excuse. One by one they were replaced with new technicians, fresh of mind-- until all what remained was ancestral, institutional knowledge that this thing was touched by evil. That trouble and danger surrounded it. DO NOT TOUCH, though to their blessed sanity, they never knew why.

Even Marie took the knowledge of the madness with her when she left Initech shortly thereafter. She had given strange thought to properly documenting the incident in extreme detail, and had even started to do so. But she thought better of it and stopped, and she would have destroyed her ticket database had LinkedIn not so suddenly seized her.

And thus no single person had complete knowledge of what had happened, nor the means to piece it together. No one knew the full extent of what transpired that fateful and horrible day, or the true nature of the shadow hulk of a server that the day produced.

No one, but for myself. I have gathered together all evidence and documents I could find of the incident. The flood of ticket requests from the day. Work orders from the IBM cultists who faced down The Thing. Marie's incomplete post mortem. The seemingly random spattering of bug reports that trickle in even to this day. All on this USB stick. With it shall go this blog of mine-- this test of my own sanity, wherein is pieced together that which I hope may never be pieced together again. I have looked upon all that the ERP has to hold of horror, and even the satisfaction of system administration and the acquisition and adaptation of new technology must ever afterward be poison to me. But I do not think my tenure at Initech will be long. As Marie went, as so many poor technicians went, so I shall go.

Let me pray that, if this blog outlasts me, my web administrators may put caution before audacity and see that it meets no other webserver.

I know too much, and still-- in its cage at the Server Room, the patchwork ERP waits dreaming.

[Advertisement] Make your team a DevOps team with BuildMaster. Pairing an easy-to-use web UI with a free base platform, BuildMaster gets you started in minutes. See how Allrecipes.com and others use BuildMaster to automate their software delivery.