Hello everyone. Data is critical to any organization and SQL server with various cap capabilities helps to process data to run day-to-day business. In this presentation, let's see how NetApp can help to efficiently manage your SQL server database. My name is Manohar Kulkarni. I'm a senior technical marketing engineer and I'm a database theme primarily working on SQL server database and opensource database.Before I deep dive into the session, here is a note on confidentiality, I will take a pause and have you go through the slide for a few seconds. Okay. To set the stage, as per the market research, we all know that by year 2025 or 2026, data volume growth is expected to be around 175 zetabyte and proportionally it would grow for database as well for SQL server. As size increases, there would be a transformation in SQL server world and SQL server count would increase and customer running any old legacy database would also look to migrate to a SQL server and this way SQL server adoption would grow at a rate of 10% kagger or so. And as database crown increases, managing them would be a complex. It's not only about managing database in your environment, but securing is also important. In 2023, report suggests that there was a data breach which costed companies an average of $4.4 US million, which is up by 2 to 3% compared to previous year. Such attacks could impact databases.And as a DBA, you should be able to quickly recover databases when such incident happens. And that's a bigger challenge as database size are increasing. Restoring could take a lot of time. So here is where NetApp has an advantage.You can offload your task to a storage to a larger extent. NetApp provide various features and solution to manage SQL server database efficiently and protect and recover database quickly from any service disruption and that's what we are going to discuss in this session. This is the agenda. I'll start with summarizing your core ontap values for SQL server. Then I'll talk about managing database on onap platform. Then I switched to high availability where I'll talk about how you can modernize approach to protect and secure your SQL server database and then maximizing your availability on O on finally managing SQL server on a hybrid cloud storage. So to summarize you know about NetApp on values for SQL server there are four main areas. Firstly, ONTAP system are highly performant. I have a summary graph to show the performance. There are very few storage vendors who publish their performance result and over the period we have introduced support for NVMe or fiber channel and NVMe over TCP through which you can scale out your workload. With more workloads, you are able to run on a few ontap platform. You can consolidate your environment and achieve a better total cost of ownership.Then efficient management. There are many ways you can improve your storage efficiency. Flex clone is one of the example. You can create a copy of your database in few second and also it does not consume any initial size or for any read operation. ONTAP has its own way of storing data and because of which overall workload running on SAN environment you should be able to see 4 is to1 efficiency ratio and thus improve your space management protecting and securing your database. It's not only about how you protect your database but how quickly your database can be protected and restored quickly. There is where NetApp manageability product like Snap Center helps in performing app consistent backup of your databases.Backup process is completely automated. Then if you want to secure your database backup, you can use ontap snap lock feature. Typically if you are taking a backup on a file system, then they have a lesser defensive layer. If your Windows system is attacked, then the attacker may encrypt or delete backup file, which would be your last option of resort to recover your database. This is where leveraging ONAP snap lock feature helps in securing your snapshot from being modified. Finally, hybrid cloud support. ONTAP have its flavor on premises and cloud across the three hyperscalers like Azure, AWS and GCP. With our control plane, you can move your database across the site and public cloud efficiently and continue to use services to manage data. So I'll start with uh managing database on NetApp on platform. Onep data solution can run on multiple NetApp platform. Each platform can serve your business need based on the IO and latency requirement. We have hybrid flash fast box which have a combination of SSD and SASbased drive. This generally provides a latency between 5 to 10 millisecond. Then there is a flash array. It's a C series. It's a capacity flash that was introduced in this year. This generally provides a latency between 2 to 4 millisecond and that's most of SQL server requires such latency. If your application and business demand extreme low latency and high IO throughput then there is a proven AFA series which provides a sub millisecond latency. All these three platform fast, AF C series and AFF A series provide a unified storage platform like NAS, SAN and object storage. SQL Server 2022 introduced support for S3 bucket. You know, by the way, SQL Server 2022 was introduced last year. And with the introduction of support for S3 bucket, you can save your SQL backup onto S3 compatible devices or even you can use it to read or import files to perform some analytics. So now since object storage is also supported onplatform, you don't require a separate storage to save your files on the object storage. All can be saved on a single storage which is a netapp on tap platform. If you are looking for a dedicated sandbased system then there is all sand array ASA. There is no NAS protocol in ASA. It provides a similar submillion millisecond latency in ASA A series and in this insight ASA C series was announced which is under C series family that provides 2 to 4 millisecond latency.There are other platform that uses ONTAB like cloud volume on that can be spinned up from a marketplace on any of the three hyperscalers like Amazon AWS, Microsoft Azure and Google Cloud Platform.AWS FSX for ONAP is a firstparty service running on AWS and Azure Netup file is a firstparty service running on Azure. So one of the common question I receive is you know what's the difference between the ASA C series and the ASA A series.So let's first look at the performance summary between the AFF A series and C series. Again, very few vendors publish each such results and whenever there is a new features related to database uh that is being released or a new platform is being introduced, our team does an extensive test and review the performance. So you see the difference between a series and C series. The graph that is being projected includes 70% read and 30% write and the trajectory is similarexcept the latency in C series is always starts from 2 millisecond and once it max out then the latency source. So more details are available in our TR 4976.This testing was not just IO testing but SQL server were deployed on a ESX host and then a load was generated across the multiple hosts simultaneously. And when it comes to write performance it is in par with the A series because right happens on NVRAM. So you won't notice much difference. The difference in the read performance is because that's how the AFF C series is designed and architected and mostly it's because of the QLC drive usage in C series with regards to comparison between AFF and ASA. Now both system uses Alua industry standard which is asymmetric logical unit axis. AFF is subject to NAS and SAN and also object protocol. ASA is used for sandbased workload. AF or ASA system are deployed as HA pair. Now in AFF learn is owned by a single node and a learn path that is advertised from a primary node is a active optimize whereas the learn path advertised from the other node is active nonoptimize. In case one of the controller goes down and the failover needs to happen which could take some time in second may not be acceptable by a businesses which are running a critical application.So that's the difference in ASA which has an ad advantage learn is symmetrically accessed from both the nodes in Hair and the L path are advertised as active optimized to the host. If the controller goes down then there is no failure operation that happens. The IO is then responded from the other secondary controller and thus saves the time of failover process. So once you know you have uh got a fair idea about the different types of platform that are available and the type of workload that you are running you would choose theright platform and then you know deploy your databases. Now once you're building your database you know managing your storage is also important. Few years back know all the IT administrator consol did a analysis and worked on consolidating their environment to save on the licensing cost. So if you had 100 SQL server you could consolidate it to maybe 60 or 70 SQL server host. uh this way not only it saves on the license but also on managing your SQL server database but your storage would still uh would still continue to consume as it is even after the consolidation but storage isequally very important because it is expensive. So there is where you know there are multiple ways in netapp on tab how you can save your space on the storage. So let me go into the basic if you're not familiar with the net app efficiency. It starts with the snapshot. As a database size are growing managing and retaining backups consumes a lot of space and time. With snapshot you just take a copy of the point in time of the blogs where your data is residing and the on tab does not consume much of a space in that case. This way you can retain a lot of snapshot on a primary storage. By the waythe maximum number of snapshot that you can store on a volume is around,8 snapshots.So typically if you have around 10 terabyte of database and you have to retain for 7 days then you would consume 7 times the size of your database on your storage with snapshots you won't consume that many space and apart from that you know uh there is also a flex load now DBA would have to create a copy of your database day in and day out on your development end machine or on a staging for reporting purpose or for testing purpose which not only consumes additional space but it takes a lot of time especially if database size is large and multiple databases need to be managed. So with flex clone DBA can create a copy of your database instantaneously and it doesn't consume a lot of space. Apart from this, NetApp has its own way of managing your storage uh data on disk. Compression, compaction, and dduplication. Since SQL server data are unique, you won't see a benefit with dduplication. But with compression and compaction, you could see a benefit especially if uh you have notturned on the compression on the SQL server. All right. So once your data base is deployed ontap system, next is to protect your database. So you could have your SQL server that could spread across multiple sites on multiple ESX host or virtualization platform that you're running. Your SQL server could be running on a standalone instance or it could be running on high availability instance like failover cluster instance or always on availability group. And to take a full backup quickly or perform a restore, I suggested earlier to go with the volume snapshot. So you know volume snapshot doesn't ensures that it's an database consistent backup. So you need to leverage some of the Microsoft framework to ensure that whatever backup you're taking via third partysupport should be an app consistent backup. So here is where we have NetApp snap center. Uh it's a graphical interface. It's a web-based interface that is deployed on any of the Windows VM through which from a single pane of glass you can manage and protect your databases across any of the site. So you can do a full backup, you can do a transaction log backup and then you can also do a point in time recovery. So the way NetApp snap center works is it leverages NetApp on APIs to take a volume level snapshot and before that it leverages SQL server VDI framework to quiet your IO and once the volume snapshot are completed it releases the IO for your transaction to continue as is. So this way with snap center you're able to take an app consistent backup. Now along with the backup and restore process, Snap Center have also integrated on tap flex clone capability through which you can create a copy of your database within few seconds. And it not only creates a copy, it also mounts your database onto your desired host.And uh if you have configured a replication feature on the storage where you're copying your snapshot or data from one site to other site then snap center can be made and aware of this. So you know tomorrow if you have been taking a backup and if you have been taking a copy of your snapshot to multiple sites from the snap center topology view you are able to get a view know how many snapshots are available at the primary site and how many snapshots are available at the secondary site. This way whenever you want to perform a restore you can restore from primary or from the secondary host. As many organization are going or modernizing their backup strategy, many of them want to take a backup to the object storage. And with snap center you can take a copy of your snapshot and copy it to the cloud object storage like Azure blob or AWS S3 or Google bucket Google cloud platform bucket or even know if you have or you're leveraging NetApp storage grid you can copy your snapshot on premises to the NetApp appliance the storage grid and the complete process is orchestrated via NetApp blue XP which is a software as a service. So with this approach you can perform a restore on demand. Uh you know traditionally if you have been taking your backup to a tapes you had to wait multiple days or had to spend a lot of time to perform the restore. Now with uh you get a more control since you're taking a backup to the object storage and snap center maintains all the history details of how many snapshots are available on prem and also how many snapshots are available at uh within the object storage. So you can perform restore from any of the location. So, snap center is supported on the storage uh which uses ice based protocol or FCbased protocol. Now, if you have a database that is hosted on SMB share or if you have a database that is hosted on Azure NetApp file in that case you can leverage Microsoft TSQL snapshot command. Now last year when SQL server 2022 was released they introduced a new feature through which you can acquires your IO's or you know suspend your IO's for a time while at the background you can take a volume level snapshot. So that is now easy to perform for a DVA andyou know you don't have to first suspend your IO onto the SQL server then you need to have a knowledge of a storage or you need to get an access to a storage console where you have to trigger a volume level snapshot you can do it by leveraging onap rest API. So the rest on rest API can be called from your host from where you're running your SQL server and then trigger a volume level snapshot and you can trigger it not only for individual database but if multiple database are residing on a same volume or on the same L then all those databases can be backed up all at the same time. So let me show you a quick demo uh so you understand how the process work. So let's say you know I have a SQL server database. It's a customer database and you see it's around 1 terab byte of size. And if I look at the its history you see when I had taken a backup on the disk it took almost 102 minutes which is almost 1 and a half hour to take a backup of 1 TBTE. So instead of this uh taking a native backup approach, let me show you if you have taken a volume level snapshot and also leveraging SQL server TSQL snapshot. So this is a PowerShell command. Uh it's a sample script. So basically what it does it first have a variables defined about the storage detail like what are the uh the onap cluster detail the credentials to connect to the ontap and this is where I'm calling the storage APIs where it first discovers the volume on which the database data files are residing and the next set of function is where it's calling the storage APIs to take the snapshot of that volume. The format is almost same you know if irrespective of the size or you know the type of databases you want to take a backup and then the next set of variables are calling or connecting to a SQL server and suspending the IO and after the volume snapshot is taken we'll release those IO. Okay. So now uh this is the snippet of the code which will connect to a SQL server and call those variable to suspend the IO and the next set of code is to trigger the volume level snapshot and then release the IO. Okay. So this way I'm going to run the script. So I'll pass on the pass password to connect to the storage system. Obviously this can be further automated and you know all these variables can already be in inserted into the code itself in an encrypted way and once it connects we'll pass on the other parameter what should be the snapshot name that's it now it will connect to the SQL server host put the IO in the suspend state and then take a volume level snapshot and it will release your IO Now if you look at the history of the backup events, you see the backup completed within few seconds as compared tothenative way of taking a backup had taken which had taken around 102 minutes. All right. So that was how you could protect your database either using snap center or you can leverage SQL server TSQL snapshot. So let's come to the section where I'll talk about the modern approach of configuring high availability and disaster recovery solution using NetApp technologies. So typically when you're configuring high aity or disaster solution via SQL server you could use the traditional approach which is lock shipping which has been there from many years and similarly with the failor clustering setup and availability group was introduced almost 10 12 years back from SQL server 2012 onwards where you have a copy of database across the multiple nodes and you would configure the listener on top of it. So at a time in availability group you always have a primary copy and depending upon how you have set up you could have multiple or a single replica copy and if disaster stuck you will have an secondary copy available to serve your IO or for your application.Now if it is a single database or even 10 or 20 database youeasily configured via SQL server no multiple options are available but if you have hundreds of databases confering high availability solution for all of the databases is would be a challenging uh task. So here is where you can leverage NetApp snap mirror technology to replicate all your data file related to your database to be copied over to the secondary site. Now there are multiple flavors within the NetApp snap. Either you can leverage asynchronous which by name you could assume or guess that based on the policy and the frequency you can copy your data from one site to the another site. And then there is snap mirror synchronous where you can ensure that whatever data that is written on site A will be first replicated to the site B and once site B acknowledges it that the data is returned into the site B then only the transaction in is completed in the SQL server. The replication of data from site A to site B is consistent if your data are residing in a save volume. If you have a very large database and you have configured your databasefile onto multiple volumes then there are chances that not all the volumes would be replicated at the same time in a consistent manner to the secondary site. This is where CG snap mirror, consistency group snap mirror helps you to consistently replicate your data from site A to site B. And this feature is available from ONAP 931 feature that was released in the mid of this year. Now the best practice when you configure consistency group snap mirror is it should not be run at a regular frequency because when the CG snaper kicks in the background it actually stalls your IO and then it takes a CG snapshot that is replicated to your secondary site. It is recommended to schedule your CG snap mirror updates during the off peak period. So with the understanding of how snap mirror asynchronous work and snap mirror synchronous work there are multiple combination how you would configure your HA or disaster recovery solution. You could configure a three-way DR which is nowadays have become common because of the compliance purpose wherein you have a cluster A setup and you are configured a snap mirror synchronous replication to your near DR site and then there is a another site which is a farr and in that case you have configured an asynchronous relationship between your primary cluster and your third cluster. There are scenarios where know you don't want to put an additional load on cluster A. So you could configure your replication in such a way that you would first copy your data from cluster A to cluster B which is your nearDR site and then you would create a uh a replication from cluster B which is your near dear site to a far dear site. Now that relationship or that topology is called a cascading relationship which is also supported and it also work from the onap level. We have an exhausted TR that explains more detailed information and the TR number is 4832. Now in this whole process you know you could be wondering you know I spoke about security at the start of the session. Now how ONAP would handle the security uh when you have configured your snap mirror replication on provides couple of features through which you can protect your uh database snapshot and one of them is snap lock snap wall snap lock is traditionally used only for the NASbased workload that is once you have written your data onto the snap lock volume and it's been committed to the storage. You cannot modify or delete those file and that's why it is preferably used for NASbased workload. But when it comes to sandbased usage, what snap lock can be used for is the snapshot that are created on your primary site can be copied over to your secondary site and store it on the snap lock volume which is a snap wall destination.So the sand snapshot that is there on your snap block snap destination cannot be deleted or can be tampered with. You can obviously perform a restore from the snapshot that is residing in the snap lock snap volume to a different volume but you couldn't clone it until on 912 version. So in onab 913 version you could create a clone copy or of this snapshot within the same volume and while creating a clone copy you just need to specify a parameter called non snap lock volume. So this way while it creates a clone volume within the snap lock volume. With this parameter, you could easily create a copy of your database and then you can use it for you know either testing or if you want to recover a subset of your data from your table you could easily do it. Okay. And then the other feature that is there in the ONAB that is the tamper proof snapshot which was introduced from ONAP 912 last year around same time where whatever snapshot that is created be it on the NAS base volume or on the SAN volume you can create on the primary as well as on the secondary. While creating a snapshot you can specify for how long the snapshot needs to be locked. So no admin user can delete that snapshot. Not even the support team from NetApp can delete that snapshot. So the difference between the tamperroof snapshot and the snap lock snap wa is the tamperroof snapshot is configured at a granular level which means you know you can have individual snapshot that are created can have a different a snap lock expiry time. Where has for the snap lock snap wall destination is at the volume level. So what is configured at the volume level the snapshot will be protected for that particular time frame. So let me combine this snap lock and tamperroof snapshot with the three-way DR setup. I will go back to the previous example where we discussed about the threeway DR setup and in this case you can enable snap lock snap walt uh feature on cluster C or you could also enable tamperproof snapshot on cluster C to protect your snapshots. So with that, let me show you a quick demo ofa three-way DR setup along with the leveraging the uh snap lock snap snapshot. So I have a multi-ight DR that is configured. I have a SQL server database. It's a lab database that I'm running.And just to show you the properties, uh it is around it's a small database. and both the data file and log file are residing on the same volume. The next is uh I'll go to the system manager and this is the volume where my data file is resetting. This is the cluster one. And now let me go to the cluster two where I'll first show you the volumes. So I have two volumes that are residing on a different SVM. There's a far DR volume which is residing on an SVM which is a snap lock volume. And then there is other SVM which is a near DR volume which is residing on SVM NDR. Now let me show you the replication relationship.So there are two relationship. So I have considered my secondary cluster as a near DRS SVM as well as for the far DRS SVM. So the first relationship is a snaper synchronous between my cluster one and cluster 2 for SQL data 01 volume and the secondary relationship is between SQL data 01 from the cluster one to uh the SQL data 01 FDR which is a snaper asynchronous. So let's assume that I first break a relationship between theprimary clusterone and the secondary clustertwo and once the relationship is broken the secondary volume which is a nearear volume becomes a readr and that volume is then mounted onto SQL server host and is made available for end user to perform readrite operation. Now once that is done I need to create a relationship between my near DR volume as well as the farr volume. So for that I need to drop the existing relationship and recreate relationship with the near dr side and the farr volume. So I'll use the command snap mirror create providing the source path as SVM NDR followed by the volume name which is SQL data 01_DR and then provide the destination path which is SVM FDRI mean SVM SLC which is a snap lock volume uh and then the volume name has SQL data 01 FDR followed by my uh type as XDP and the schedule to run every five minutes with the policy type as XDP default. So once the relationship is created, next thing is to refresh my relationship. And once that is done, you will see all the new snapshots that are created on my near DR volume will all get replicated to my snap lock volume. So now I will switch over to my volumes tab. You'll see once my relationship is in sync uh these are the latest snapshot that I created on my near DR volume and the same snapshot will also be available on my snap lock volume. That's how easy it is to configure or work with multi-sight disaster recovery. Now let's say you know if I am trying to delete one of the old snapshot from my snap lock volume it will give me an error message that this snapshot is protected with snap lockfeature. So this way you are also able to protect your snapshot. Now to show you the property of this snap lock volume I'll go into the its property of snap lock setting and you see that I have set a retention of 8 hours. Obviously in an actual scenario you would set somewhere in months or days depending upon how for how long the snapshot needs to be protected. Okay. So with that you know uh let me go into the next section that is business continuity for SQL server using net app solution. So uh as we saw in snaper asynchronous or in snaper synchronous whenever a failover happens you need to manually mount those volume onto the host and bring the database up. But with this business continuity solution which is Snapner active sync which was formerly called as Snapner business continuity whenever a disruption happens the failover would be automatic and IO would be served from the secondary side. So to explain with the setup you know let's say you have two stoages across different sites storage A and storage B and a database data files are spread across multiple volumes and those volumes are synchronously replicated to the secondary storage. Then you have a mediator which keeps a check on the health. Okay. And the L paths from the storage A as well as storage B areyou know are advertised and are connected to your host but only one L path will be advertised as active optimize which holds the uh ownership of that L. Let's say if your storage A goes down in that case mediator will come to know that the storage A has gone down then a failover place will take place. It will be a transparent failover and then the IO will start responding from the storage beam and at that same time the learn path that are connected to the host will be advertised as active optimizedand that's how the snap mirror active sync work. It is very simple to set up and your transaction will continue to run as is. the IO which was responding from storage A will start responding from the storage B. So let me show you with the example. I have a SQL server uh setup and I'm going to create a load on one of a database. It's called sales database and I'm going to use hammer DB to generate a load on this database. The database size is somewhere around 5GB. I'm connected to Hammer DB. I'm going to generate a load on this database. So as I'm running the process, I'll go in the SQL server back end to show you how many users are connected. So there would be somewhere around 12 to 13 users. And as this is performing, uh these databases are hosted on a VMware platform. They're on virtual disk. So I'll now connect to the secondary cluster host to show you the replication status. So this is the relationship status. Uh the primary is the cluster one node where it has configured a consistency group. It's uh the consistent group is SQL prod which have a bunch of volumes and I'll initiate a manual failover. In this case the relationship is in sync status since it's a synchronous replication that is happening and let's say a disaster is going to occur. So I initiated a failover process and at the same time I'm also going to fail over my VM using a votion. So during the process I will provide the detail of the secondary ESX host where the VM needs to be moved and while my VM is moving my storage is also being moved forthe volumes are also being responded from the secondary storage.So let me go to the cluster one uh system manager and show you the relationship status. Now you see that SQL prod destination has become my primary and my SQL prod which was earlier my primary has become secondary. All right. So while this migration switch over uh let me connect to the SQL server and show you whether the processes are still there. And you see the process are still connected. There was no disconnect and nope. uh that's how you would be able to perform your operations without hampering your applications. Okay. So coming to the last part of this session that is managing SQL server on your cloud hyperscaler. So uh as I mentioned earlier your databases could be on premises that could be using net app storage and if you have public cloud then onap has its present and its flavor on the public cloud as well you could be either using cloud volume on running on either of the three hyperscalers or on or using the first party service from Amazon which is AWS FSXN onif you want to just manage the storage. Then there is a NetApp Blue XP which is a software as a service. It's a unified control plane that is managed by NetApp. From NetApp Blue XP, you just need to add your storage be on prem or on the cloud. From a single pane of glass, you could manage your storage operations. You could create a snap mirror replication or you can configure your volumes. Now uh the databases data files that are hosted on any of this NetApp storage can be managed by using NetApp snap center either to protect your database or to copy your database from one site to the other side or to perform a clone operation. So using NetApp blue XB to manage your storage and NetApp snap center to manage your databases from few screens you are able to manage your database across your cloud hyperscalers.So that was my last slide. So uh here are the key takeaways. So I initially started off about talking about the how you can improve your total cost of ownership by understanding different platform and then implementing your data based on uh different platform based on your requirement. I also talk about different features within ontap through which you can improve your space management. Then we looked at how protecting your database and maximizing your availability. protecting your database either by using NetApp snap center or by leveraging a combination of ONAP rest API as well as SQL server capabilities to take a TSQL snapshot and then maximize your availability using Snap Mirror technology and the business continuity solution with snap mirror active sync. Then I talk about securing your database by leveraging snap lock prop features either by using time for proof snapshot or by using snap-lo snapw featureand this could be beneficial for securing your database snapshot especially if it is a mission critical data. Thank you for watching this session. There are uh some other related session that could be of your interest and we have TR's technical report that talks about best practice of implementing SQL server ontab. You can stay connected by reachingout to us. Uh you can reach out to us by through your NetApp sales representative. I would be happy to help you.Thanks for time for checking out this session. Have a great day.