Sql Server 2012 Standard Torrent
Milan Skidmore
So as they say, everyday is a school day. Today I learned that my workplace, runs SQL Server Standard edition, where I would have assumed Enterprise was in place. Although in reality shouldn't be surprised!
For some context, we have a very large database that houses our warehouse data. As the database has grown to a large size, it's causing issues with space on the server along with some application performance. So looking at it from my perspective I suggested we archive and purge the PROD database, to house only 18 months data in the PROD environment.
Wrote my scripts and tested them and all fine. I then went to compress the tables I had deleted data from, to find error messages that compression is not available in SQL Server Standard and requires Enterprise edition.
Wondering what my next steps are here? My assumption is that even though I am deleting a lot of data, we won't actually benefit in terms of performance, and space requisition until the tables get compressed.
Since 2016sp1 all the compression features have been available in all editions, though that may not help you at all as upgrading your production environments (I'm assuming they run 2014 or earlier?) isn't something to just do on a whim.
If you are deleting the data is compression really what you need? If it is, then to suggest how you might reduce the size of the remaining data we'd need to know a lot more about that data and its use.
Shrinking is usually not what you want to do so you are correct to be cautious - if the data could grow back to use the space again then you might as well leave it allocated to the DB as this will avoid the future (potentially performance harming if they happen at an inconvenient time) growth operations, and the shrink process can cause significant fragmentation especially if (as I've seen done) the data files are shrunk on a regular basis.
But if you have reduced the data size of the database by archiving/deleting a large amount of data and you don't expect the amount of data to grow back to close to its original size any time soon, a one-off shrink should not have significantly concerning side-effects. But shrink to how large the data is expected to be after a reasonable length of time, don't shrink down to the smallest the current data will fit in.
Some general info
- Each host needs to be licensed.
- A minimum of 8 core licenses is required for each physical processor and a minimum of 16 core licenses is required for each server.
- Core licenses are sold in packs of two.
- Standard Edition provides rights for up to 2 Operating System Environments or Windows Servers containers with Hyper-V isolation when all physical cores in the server are licensed. For each additional 1 or 2 VMs, all the physical cores in the server must be licensed again.
- DataCenter Edition provides rights for unlimited Operating System Environments or Windows Servers containers with Hyper-V isolation when all physical cores in the server are licensed.
More tomorrow when I hopefully share the link to the recording and some answers to questions. The webinar today talked a bit more about FCIs in SQL Server standard and utilizing block-level replication tools; SIOS Datakeeper is the tool we prefer, though there are others.
Main differences between web and standard are the max memory and max number of cores. Both web and standard version don't have the possibility of online indexing, this is only possible in the enterprise edition.
For a database size having a size of around 200GB, the web edition looks to be ok. But this depends also on the workload of the database, are just some users querying this database or is this database used by thousand of users.
If this is a large environment then the answer is most likely datacentre - as you can run unlimited windows OSEs and therefore move them between hosts etc. if you go for standard you cannot move the vm without SA etc plus when you add a vm you will need more licenses.
Standard, even if you are using Datacenter to license the VMs, unless you KNOW you need Datacenter features in the VMs. There are very few feature differences between Standard and Datacenter VMs, so this is unlikely.
If you use Windows Standard to license hosts, you can still move VMs without SA, you just need to have enough licensed capacity on the hosts at all times. SA does not provide license mobility for Windows Server.
Licensing Microsoft guest VMs works same in VMware and Hyper-V. You cannot legally run a Windows 20XX Datacenter VM on a host that only has Windows 20XX Standard license(s) assigned to it. FULL STOP.
I still recommend that you use Windows Standard as the guest OS. Maybe you need to move VM to a 4th host, even temporarily. If the guest was Datacenter, then you would NEED to license the host with Datacenter, as opposed to the much less expensive Windows Standard license.
AWS on the other hand provides the server, back-end networking, storage, Multi-AZ (if selected) - basically the complete package and ready to use "server" that too fully licensed - On top of that, RDS also offers features of no hardware maintenance from customer end, patching etc so that customer can focus only on the application code - comparing that only with the software license - the price is bound to go up.
- The only difference between Standard and Datacenter is the licensing, otherwise both are identical bits and bytes wise.
Datacenter in Server 2019 the only difference to standard is regarding to the virtualization rights. With one Datacenter license you are allowed to install one Hyper-V Host and run an unlimited number of virtual machines on it (Datacenter or Standard). In the older days there where more differences in terms of how many CPU's the OS supports and what the maximum RAM supported is, but with Server 2019 it all got the same (64 CPU's and 24TB of RAM): -us/windows-server/get-started-19/editions-comparison-19
We are planning to create a dedicated server for SSIS. SQL 2016 will be used for this. But I'm confused on selecting the edition of SQL, Enterprise or Standard. Please shed some light on this.
I read on the MS article mentioned below. Still confused!
-us/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server?view=sql-server-2016
Thanks,
SQL DBAWhat are you confused about? Both editions work with SSIS, but Enterprise provides more features, as shown in the link you provided. If you need any of those features, you need Enterprise.
The Oracle Attunity drivers are specific to Enterprise and are faster than the standard oledb/odbc drives supplied with the Oracle client.
Whether it justifies the extra cost is hard to say but depends on your volumes and on how fast you need data loaded/extracted from/to Oracle.
If volumes are low and time is not of major importance then do not go enterprise just for this.
This standard applies to all servers (including production, training, test, and development servers) and the operating system, applications, and databases (unless explicitly excluded) defined by this standard that provide services to the RIT community.
Servers participating in High Performance/Distributed Computing/ grid computing should employ appropriate and documented safeguards to protect RIT Confidential information and access to RIT internal networks.
This is leading to confusion for SQL Server customers. It would be better for Microsoft to update the documentation to reflect that SQL Server Standard is NUMA aware. Perhaps add an additional footnote, as they have footnotes for other features in that same section.
We are in FEBR 2022 and they have not changed a thing in the documentation where the compare all the versions. -us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15
Microsoft handed everyone a big gift with SQL Server Standard Edition 2019. The Standard edition of SQL Server did not previously support encryption. Surprise! Now it does. Prior to this new version, SQL Server Standard customers had to upgrade to the Enterprise Edition, or install a third party encryption solution. Upgrading to the Enterprise Edition was expensive for many small to midsize Microsoft customers, so bringing encryption to Standard Edition with 2019 is a big deal.
Microsoft implemented encryption in Standard Edition by bringing the EKM Provider architecture from the Enterprise Edition to the Standard Edition. This means that Standard Edition users have access to the same encryption and key management capabilities that are available in the Enterprise Edition. This is great news for Microsoft customers as most are running both Standard Edition and Enterprise Edition in their IT infrastructure. You can now deploy the same encryption and key management solution across your Standard Edition and Enterprise Edition databases. If you are using Transparent Data Encryption (TDE) in the Enterprise Edition, you can now do the same thing in Standard Edition.
The new encryption capability for Standard Edition is only in the 2019 release (version 15.x). Earlier versions of SQL Server Standard Edition will not be upgraded to support encryption. To take advantage of encryption in Standard Edition you have to upgrade to the 2019 release. You do NOT have to upgrade to the Enterprise Edition!
b37509886e