 My name is Alex Friedman, and what we're going to talk today is multi-tenancy in today's world that includes not only on premises, but in the cloud, search, and a wide variety of databases. I will start with some definitions, then we'll go through the business drivers, the motivators that result in multi-tenancy. The requirements that are implied by the business drivers, and then talk about different design alternatives that we have. And after that I'll talk about two real world case studies that involve multi-tenancy. So what is multi-tenancy? It's a situation where many users, typically unrelated, can use a shared resource. You can think of multi-tenancy as the relationship between the building owner and tenants. In an apartment building, each tenant can customize their own apartment to a certain degree and feel secure behind their door. Because the building owner can distribute the costs of property maintenance amongst the tenants. He can keep his costs down and keep rents reasonable for the tenants while still making the profit. So there is an implementation spectrum. At the two ends of the spectrum is virtualization where nothing is shared and all data is isolated. And on the other end of the spectrum is communal multi-tenancy where it's like two more student roommates that share two beds and you still have to wait in line even if you're late for your class. And one more example on the virtualization is web services for Amazon and for the communal is Salesforce.com. I have background in data modeling and as a data model I always tend to think about multi-tenancy. What's the big deal about multi-tenancy? All you have to do is just add another entity called tenant and you're done. But there is much more and the problems are more than of different types. So my experience is mostly with five to two hundred client business to business applications. And next couple of slides will go through business drivers that resulted multi-tenancy and corresponding requirements. When your company is moving from shrink-wrapped software to hosted services it has big benefits of that move. You can use economic scale to provide better service to your customers. Your customers get benefits from you but you still have to convince people that work for those organizations that they have to move and they have to switch, change their approach. So the requirements when you migrate to hosted services they're not only technical like you have to buy a tool set and process to get customer data to the cloud. You have to create development environment, set up send box for customers and provide management tools but also you have to have online documentation and you have to motivate your customers to get there. Adapt customer service model. Here it can be that it's either a case of an organization that is tired of babysitting their customers or it can be an internal IT department that is swamped with all the development work and what they want to do is instead of developing all the code they want to move away from it and then they can focus on providing just a process template, supporting an ecosystem, promoting best practices and your empowered customers become the tenants. The requirements correspond you have to version the whole technology stack you have to create customer community marketplace to share ideas and components design knowledge database you have to be able to resolve customers mishandling the environments so you have to have an easy way to roll back to the state of the environment before a disaster took place. This motivator is for the company that is involved in analytical services. The core business for the company is in generating knowledge and providing actionable recommendations. However, most of the time is dedicated to processing the underlying data and mature organization of this type should move away from heroic efforts of the analysts to standard offering based on a common database. If you have a significant customer base the byproduct of the common database is that you can offer industry-wide or counter-wide benchmark analysis. You need to combine analyze data in constantly evolving ways and you still need a widely used and easily adaptable front-end tool to avoid steep learning curve and make sure that your analysts are productive and you have to make sure that it's possible for your analysts to augment your common database with additional data sources. The case studies in this presentation will cover previously mentioned motivators. However, since I submitted the presentation for this conference my role has changed and I'm currently in an organization that completely outsources IT department and in that organization again you see the need for multi-tenancy where data warehouse operations and projects are built out and you have to make sure that you are able to effectively separate individual teams into their own environments. You have to be able to mask the actual data and being able to integrate results from multiple teams and focus on creating policies and procedures to ensure consistency across deliverables. So when we started we talked about two extreme cases. Everything is shared and nothing is shared. But there is actually a spectrum of implementation possible scenarios for implementation and if you look at the stack database that consists of server, database and schema you can see four different categories and from left to right each next category has more shared components. Depending on the approach you take you have different set of challenges. In an isolated approach they are mostly infrastructure related you need to achieve full automation of all administrative operations including customer onboarding, provisioning, capacity monitoring, resizing and database upgrades. The problems in the shared approach are mostly about tenant isolation, customization, resource governance, security, backup restore and application upgrades. As challenges for the isolated approach are addressed by various database vendors and service providers I'll just focus on the shared approach and the solutions are detailed in the next couple of slides. So that isolation means that the goal is for tenants to operate in virtual isolation as though they each have a separate instance and to avoid any malicious or accidental data disclosure. And solutions include a combination of three components using an intermediate layer between a tenant and data source making it appear to the tenants though its data is the only data in the database. Using access controllers to determine who can access data in the application what they can do with it and obscuring every tenant's critical data so even if someone gets a hold of data they don't know what's there. For the customization you have to be able to extend your database and there are different approaches to do that. One approach very widely used is that you create tables, white tables with a lot of columns that don't have any names just pre-allocated fields and you create a metadata layer in between that tells what the meaning for each of those columns. Another approach is you can create metadata structures so instead of having one column, one field for each column you have one row for each attribute. Or you can also do you can look at all the industries that they support and think through what is the universal business model that you can have. This way you will be able to capture most of the attributes right from the start and then use one of the other approaches for the rest of it. Or you can use schema res design. Code customization it's not necessary to over start with the most extensive and most complicated of creating extensive framework. A lot of big data companies what they do they create query builder where customer can go and create their own query then queries are queued for your task and executed in batch mode and the notification is sent to the customers when the results are available. Or you can provide direct database access with some starter kit. This first one here is you rarely think about that but if you want to be able to upgrade your application all the time you need to upgrade all your tenants together and it's not that easy to do. So you would need to require creating a regression test test that for each of their tenants so they can be upgraded in any moment in time. Most likely your restore and backups are not standard aware so you might be able to restore the whole database but not specific to a particular tenant. So here you can use a brute force approach and because you have this cloud with unlimited resources you can backup the whole database, restore the whole database to a temporary location and identify and copy just data for one tenant. Which is for me it was a revelation when I started to think about that. I was always in the situation of the on-premises and then in order for me to get a new hardware a new server I have to wait six months. Here in 30 minutes you have a new server and you can do anything you want. For the resource governance the test include allocator resource and monitoring and metering. One of the simplest approaches could be you can just monitor and just suspend access for the resource abusing part. When I worked with Redshift I used Redshift's work rate management tool to assign tenants to specific classes and then provide concurrency level for each of those tenants. And if the simple solutions don't work then start building your governance tools. Security. The challenges here are that you have to define security at multiple levels of grain. At operating company for industry or country for account of a business unit with an account. And you have to think about not only users but also about administrators. Administrators are global and administrators for tenants. And actually one of the slides at the end of the presentation have one of the implementations for the security. So what is the result in overheads? On the isolated side it's you have to provide additional software that manage hardware and databases. You have footprint from numerous databases in CPU, memory and disk space. And you also have a lot of unusable capacity caused by discrete server sizes and they need to size servers to the maximum possible workload of each tenant. For the shared approach the challenges are application specific software, performance heat from extra layer between users and data. The metadata layer that we talked about. Data contention. Because each of your tenants will only use a small slice within an order of magnitude larger database. And process contention. For example when I worked with Salesforce.com application they have a constraint where you cannot have your query running for longer than two clock minutes. So if there is a 100 queries running at the same time you can have no more than one second of the CPU cycles. If there is a thousand queries then you can have no more than 0.1 of that resource. This is a kind of nice slide. The shares where each factor move the needle toward one solution or another. So if you have a lot of tenants then you are most likely better with the shared solution. If each of your tenants have a small amount of data you can feed a lot of tenants on one server. So again share this performable way. If you have a number of concurrent users in each of your if each of your tenant has a lot of concurrent users then they will probably tend to occupy the whole server. So I said the approach is more to your liking. If you have to provide valuated services like backup and restore then you would probably want more. I said the approach. And perceived security risk. So if you're an industry that people think that there is a huge risk if somebody can see their data then it's going to be very difficult for you to convince them to go to the shared approach. This is a business slide. Let me go through this in detail. So this slide shows how multi-term aspects can be implemented within different databases. How much flexibility we have when we try to implement multi-term in different databases. And the horizontal bars are artifacts in the database. So for example in Azure SQL database we have server can have multiple federations. Each federation can have multiple databases. Each database multiple schemas. Many tables per schema. There is a hole here where other databases can have range partitions. And then tables can be accessed through multiple views. And the vertical bars here show different aspects that we use in multi-term. So restore here. This is for the backup and restore. I showed you restore because in some databases they allow you to restore lower level grain than the backup. So in Azure you can restore data on any level from database to schema to the table. And on this side is the security access. In Azure SQL database you can have security access defined on any level aside from federation to the views. So it's very flexible from that regard. The next one here I show oracle. The newest addition oracle. Where I see they call it multi-tenant database. And there is a lot of multi-tenants built into it. So you have to look at it. It's pretty interesting. In particular you can have one container database that can have up to 252 pluggable databases. And the restore and access have wide variety. So you can define it at any of the levels here. As you can see for MongoDB it's pretty new. And you have less flexibility. Restore can be done on two levels here. And access can be defined for the whole database within MongoDB. The next one is Redshift. This is Amazon Column Database. Access is pretty flexible. But restore can be done for the whole cluster owner. What I also want to mention here is that some of the functionality can be performed outside of the database. For example Redshift. A lot of the functionality that you need might be defined within Amazon Web Services. Okay. So we have ten minutes for the case studies. Two case studies. One are labeled self-serve ETL. One is self-serve analytics. For the self-serve ETL we needed to move migrate to the hosted environment and provide self-serve for our customers. And the goal was to get data from multiple data sources and move them to a common data format. What interesting was about this particular situation is that the same source was used by ten or more tenants. So we could create highly reusable templates. Like I show here. One template for ten or more tenants. This is a list of all the components that we need. But if we go directly to ETL requirements, we needed multi-tenant repository with two-level user hierarchy. Reusable templates. Mapping shared across tenants. Being able to feed spreadsheets created by business owners directly to ETL development process. Support delta view between different versions of the same map. Provide native connectors to selected databases. Integrate with all the other components. With job scheduling, managed file transfer software and operational database. Optimization in segment services between tenants. At the time when we did the duration, Spring 2012, we tried three leading ETL tools. Power center, data stage and pervasive. And what we found that for the execution environment, it was pretty easy and to have something that we needed for my requirements. But none of the tools were provided a truly multi-tenant design environment. We looked at four different types of data stores. Flat files in Amazon S3. Eximal structures in relation database, relation database and no-skill database. And the recommendation was to use Amazon S3 for the files from the clients for the extract files. Use no-skill database for all intermediate data stores. And use relation database for reference data. And common data was dictated by a different team, by the co-application team. The second case is self-serve analytics. The drivers here to mature analytical environment and to provide self-serve model. Data sources were the same across all the tenants. But they segmented data differently. Each of them segmented data differently. And data size was, for some of the tenants, more than one terabyte. The platform that was chosen was Amazon Web Services. And we also needed to support SQL. So from all the databases, only the news Amazon Redshift was the database that provided all that we needed. And it's a column of database. So for the accessibility, I used that white table approach. And there was no performance penalty. Because the column of database is scanned by columns, not by rows. So it was okay from that perspective. I also created carbon views on top of the database. And the view combined information from the fact table and all the dimensions. And those views were effective because the database uses join elimination. So if a particular query didn't use the table which was within that view, it didn't bring into the result set. For the executive summary, we used MongoDB. And it was very fast because it stores the whole graph as one document. Okay. So this is security integration within Redshift. And it's not common data-specific because it's a mature database environment. They had all the artifacts that you expect to see from other databases. And all the tables resided within one schema. So tables were in one schema. And on top of that schema, it created a number of views. And then created user groups to access those views. And then user can have access to multiple user groups. That way, if a particular analyst can have access to multiple clients or multiple divisions within a client, they can do it through that. And what I wanted to also notice is that for the company's access was hard coded through views. Because we onboard each customer at some point in time. So we have all the information needed. But divisions are more dynamic. They can change over time. And it's not always possible to know all the divisions. So the access there was defined through metadata through cross-reference table. Okay. And we have a couple of minutes for questions. This one. Yeah. Okay. Okay. Let's just look. Okay. Okay. One before. Okay. Okay. So here we use Excel, actual Excel. The ODBC connected through views to the database. And then there was an additional piece of software that what we used to, for the graphs that they wanted to keep, then those were stored in the dug in MongoDB. So it's not exactly to the scale. So that MongoDB was accessed by a different piece of software. Not by itself. If you want to be isolated, how that management works? Then you have to have different instances. So you have one instance where you have shared and then you have separate instances for each one that wants to have isolated approach. Then you kind of double in your efforts. Yes. Okay. So if you are in this extreme shared environment where it's all the tables are shared, then there is a small number of tables which use most of the disk space in your environment. Right. So you can just write a query that go through the course that go through those small amount of tables and see how much space is used by each tenant. So it's a small amount of space. What's the best solution to this kind of problem? If you, let's say you don't use just one schema without physical division. You can use range partitions for example. Right. So when you use range partitions and your range defined by tenant ID, then you can, for each range you can define it can go to separate file system and you can limit that particular one. Or you can either limit or you can monitor how much they use and then tell them to pay more if they use too much of your space. So what I use for, in this example for Redshift, they have a work with manager and I use that for different purpose. I didn't worry how much they use. I just wanted to make sure that they don't use everything and then everybody else would be left without that resource. So I limited how many concurrent connections they have. And you worry about how much they use. Then you can, with Redshift, you can also have metrics that tell you for each user how much a particular user used resources. And then you can combine that in your groups and you see. You can see, you can also see how much CPU is used by particular. You can see, okay, you can see all the queries that were run within a period of time. You can see who ran a particular query. For each query, you can see how much CPU used and you can combine that information. By user and for each user with an account and see how much they use. So actually that's the function of the technical articles that you see that have instance data. So while you're reading an instance, you again decide how much of a particular family there are in this common city and in the new city. Right, so that's a very interesting database from the multi-managed perspective. Because in this Oracle 12c, they provide some tools for you. Like you can have caps and shares and then so you don't have to write your own code to do that. This is a distributed database. It's relational, but it's not row based, but column based. So it's SQL based and it's relational, right, yeah, right. It supports everything from SQL perspective. I have a question on security in the next slide. Was there a role-based access which was configured for each of these layers? What do you see here is only for analysts, for users. So you don't see administrators. So I concentrate here on that and for the administrators was a separate piece. So it's row based, but it's only for one role here. So you can decide at the cluster level, at the instance level, at the table level. Okay, okay. So I created a view that joined all the tables together. And within that view, for example, there is an operation company and account and division. And for the division, I had a cross reference table, the cross reference division to use a group. So, and then when some of the access that view, they provide the user ID, user ID joined to the cross reference table and that view gave them access only to stuff that they can see. So it's at the highest level, the view level, everything was done through the views. But it is just one of the possible implementations. Yes. So what we did, we used the simplest solution possible. We just monitored how much they use. And if they used too much, we tell them not to do, or we shut them down. But yeah, it's a big problem. And if you want to be more mature in the environment, then you have to have certain tools built in for you to handle that. So you ever had a case where a tenant who wasn't in a screenshot environment and then had a desire to move to a more isolated environment? And if any of us might have similar situations? I have a similar situation where you migrate from one version of your technology stack to a different version of this technology stack, right? Some of them are ready and they move. So you physically move them to the new stack. Some of them stay behind. So I have the experience with that. But I don't have that. I don't have that. We didn't have that problem so far. Yeah, I see that you are very much interested in particular. Okay, thank you. And here are my business cards.