sql server 2014 암호화 백업
std 이상부터 지원한다. ㅠ.ㅠ~
사용자 읽기 i/o 를 제한 할 수 있다.
system write 성능은 제한하지 못하지만 정말 원하던 기능인데 많이 좋아졌다. 역시 엔터프라이즈 기능이다.
이러다 sql server 아무도 안쓰면 어쩌지....슬프다....
http://msdn.microsoft.com/en-us/library/cc645993.aspx#CrossBoxScale
Summary: SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance. This paper provides an overview of the primary changes made to the cardinality estimator functionality by the Microsoft query processor team, covering how to enable and disable the new cardinality estimator behavior, and showing how to troubleshoot plan-quality regressions if and when they occur.
Writer: Joseph Sack (SQLskills.com)
Contributers: Yi Fang (Microsoft), Vassilis Papadimos (Microsoft)
Technical Reviewer: Barbara Kess (Microsoft), Jack Li (Microsoft), Jimmy May (Microsoft), Sanjay Mishra (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited)
Published: April 2014
Applies to: SQL Server 2014
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.docx
Here we go again! It seems that it was only yesterday we were talking about all the things you want to look out for when upgrading to SQL Server 2012. Now, just two years since SQL Server 2012 was released, we have a shiny new version with SQL Server 2014. And that means, of course, people will be thinking about upgrading to SQL Server 2014. So I’ve put together a list of items that you will want to have handy when upgrading or migrating your databases from those dusty old versions to the brand new SQL Server 2014 engine.
I’ve witnessed many novice administrators make the common mistake of believing that the upgrade process for a database server is as easy as pressing a few buttons. While you can certainly click “Next, Next, Finish” and consider your task to be complete the truth is that the upgrade process is often considerably more complex. A proper upgrade process involves detailed research, planning, and execution.
Failing to prepare a proper upgrade process for your database server is likely to result in your end users seeing diminished performance after the upgrade is complete. Since your goal is to increase performance and stability as a result of the upgrade you can understand that your users are likely to be upset if things were to get worse!
Since it can be a daunting task to put together everything you need in a pre-upgrade checklist, I’ve compiled this list of items that you need to include in any checklist you put together for migrating your database server to SQL Server 2014. Including these items into your checklist is likely to help you avoid 98% of any potential upgrade issues.
Please note that these steps are specific for an upgrade to the database schema and data. They do not include anything regarding the upgrading or testing of an application that is going to be accessing the upgraded database. You will want to remember to test your application and not just assume it will work perfectly even after the database has been upgraded. I would also advise that you perform these steps in a non-production environment first because I often find that common sense isn’t so common after all.
Also please note that this list is meant to serve as a guide for you to use when doing either an in-place upgrade or when migrating from a previous version. However, I always recommend doing a migration whenever possible as opposed to the in-place upgrades. Migrations (typically done by restoring a database backup from the current instance) just make me feel more comfortable should I need to troubleshoot issues later. I like knowing I started with a clean slate, but that’s just how I roll. You should do what works best for you.
The SQL Server 2014 Upgrade Advisor (UA) is just that: an advisor. Much like a consultant, it doesn’t fix everything that is wrong, it merely advises you on what actions you should take when upgrading to SQL 2014. The actions the UA recommends will come in two forms: those actions to be done prior to a migration, and those actions to be completed post-migration. The UA is really good at finding what I call the “stub-your-big-toe” things that need fixing prior to a migration. But it is not foolproof, it will not identify every last detail. You will need to play the role of an actual DBA when migrating to a new version. Many of the items below will help you to do just that.
Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, you do now. You should review them to the point that they are familiar to you. You don’t have to memorize them all, just be familiar with them so that if something odd happens you can think to yourself “…hey, is this odd behavior listed in the breaking changes section of the Books Online (BOL)”? I would like to believe that the UA will alert you to many of these breaking changes but the truth is the UA is not as dynamic as the BOL. That means the BOL may have an entry or two that doesn’t make it into the UA checklist, and that is why you should review this section.
Currently (as of this post in June of 2014) the link above lists the breaking changes for SQL Server 2014 simple as “no new issues”. I believe this is simply because this BOL entry has yet to be updated, so check back on this page often and I am certain you will find content listed.
Similar to the breaking changes, the behavioral changes are changes that could still affect you in an adverse way. They are definitely worth reviewing, and they are also things that the UA is likely to never report back to you about because they aren’t things that *will* break, but merely things that *could* break. Also worth noting is that the BOL appears to have two entries for behavioral changes, one for SQL Server features, and one specific for the database engine. I’d advise you keep your eye on both going forward.
One of your post-migration or upgrade tasks should be to run the following statement:
DBCC CHECKDB WITH DATA_PURITY; |
This statement will check your data for values that are no longer valid for the column datatype. For databases created prior to SQL 2005 (and you *know* they are still out there), this step is rather important to take. For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.
But what about a database that was created in SQL 2000, migrated (poorly) to a SQL 2008 instance, and left in the SQL 2000 (80) backward compatibility mode? What about that little feller? Do you want to assume that the DATA_PURITY check has been getting done? Here’s a thought: just go run it yourself anyway. That way you know it is getting done.
While not as critical as the DATA_PURITY command noted previously, this one still has a place in any migration or upgrade process:
DBCC UPDATEUSAGE(db_name); |
This command will help fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. For SQL Server 2012, this check was recommended for databases created prior to SQL Server 2005. However, in SQL Server 2014, the BOL entry link lists this command as being applicable for databases created in SQL Server 2008 and later. That seems odd to me, since this command is valid for SQL Server 2005. I’m not certain why the SQL Server 2014 documentation states this as a command for SQL Server 2008 and later. I’d recommend you run this for SQL Server 2005 databases being migrated to SQL Server 2014 anyway. [If I can get an answer from someone at Microsoft regarding this documentation issue I will update this post accordingly. In fact, I see this message for a handful of SQL Server 2014 entries, and I think that it's usage is slightly misleading.]
This one is not to be skipped and is simply a MUST for any migration or upgrade checklist:
USE db_name; GO EXEC sp_updatestats; |
This command will update the statistics for all the tables in your database. It issues the UPDATE STATISTICS command, which warrants mentioning because you *may* want to use that command with the FULLSCAN option. I’m the type of person that would rather be safe than sorry and therefore would end up running something like this:
USE db_name; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN'; |
Bottom line: don’t forget to update the statistics after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and may end up wasting your time while you try to troubleshoot the possible bottlenecks. With SQL Server 2014 there is also a new Cardinality Estimator (CE), and I’ll talk more about this later, but you are going to want to make certain your statistics are as accurate as possible before you begin any testing. So, take care of the stats now, and you don’t have to worry about it later.
Believe it or not, every now and then someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. The point here is that your view may not be of data that is contained in just the database on that single instance. In what could be the most dramatic twist of all, sometimes these views are created using a SELECT * syntax.
I know, I know…what are the odds that you could have such code in your shop? But it happens. And when you have bad code on top of views that go to other databases (or views of views of views of whatever else some sadistic person built) you are going to want to use sp_refreshview to refresh those views.
So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, just like a burger topped with veggie bacon. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. Using sp_refreshview is a lot like like flossing: it doesn’t take much effort, and the end result is usually worth it.
You’re a DBA. Backups should be in your DNA. You should have taken one prior to the start of any upgrade or migration, and you had better take one right before you turn that database over to your end users. Also, you should save any output from the items listed here, as it could prove helpful should something go awry later. (bonus item – make sure your backups are good!)
Microsoft lists the minimum requirements for installing SQL Server 2014 on this page. However, those are the *minimums* there. Chances are if your servers don’t already meet those requirements then you aren’t looking to upgrade anytime soon anyway. But if you are upgrading, then it might be time to upgrade your hardware as well. Heck, you may even consider going virtual (if you aren’t already), which will still require you to examine your hardware requirements.
But here’s the real reason you will want to upgrade your hardware: new features. Let’s say that you are thinking of upgrading to SQL Server 2014 in order to take advantage of In-Memory OLTP Hekaton. I can’t find any MSDN article that states if there are minimum hardware requirements for Hekaton, but I did find this blog post from the SQL Server team that suggests some guidelines. Considering there is a lot of shiny new things in SQL Server 2014, chances are you’ll need to do some extra legwork here to scope out what hardware you’ll need in order to leverage many of these new features.
For those folks running SQL Server 2000 instances (yes we KNOW you still exist) you are not able to upgrade directly to SQL Server 2014 without first upgrading to an intermediary version. You have two options to choose from when going from pre-SQL Server 2005 versions. The first option is to do an upgrade in place to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012. The second option is to do a backup (or even detach) your database and restore/attach to an instance running SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012. At that point you will be able to complete the upgrade to SQL 2014.
And like I said at the beginning, I prefer to do migrations rather than upgrades in place. It’s just a preference. For something like a SQL Server 2000 database, I’d need to do a backup, restore it to an intermediary version (like SQL Server 2008 R2), then do another backup and restore that to SQL Server 2014. At that point you should run through this checklist before turning it over for testing.
If you have been going through SQL Server upgrades for the past ten years then you are likely to have noticed that the compatibility level does not get set to the newest version after the migration is complete. You need to manually set the compatibility level yourself. With SQL Server 2014 this becomes more important than in previous versions due to the new Cardinality Estimator (CE).
I delivered a session at TechEd this year on the new CE, Jimmy May has a short summary blog post regarding the new CE, and there’s also what I consider to be the “Gold Standard” on the new CE in a great whitepaper from Joe Sack that details the good, the bad, and the ugly with the new CE.
The TL;DR version of the whitepaper is this: you’ll want to take advantage of the new CE except for the times when you won’t. Part of this is knowing which compatibility level you are using. I’d recommend you update every database on the SQL Server 2014 instance to compatibility mode 120 and test, test, test. [This assumes that you have baselined performance for your critical queries prior to the migration, so that you can verify if the new CE is working for or against you.]
Because you’re a geek, that’s why. Take a few minutes and read the release notes. No, they aren’t as funny as the release notes for apps on your phone, but they can be useful for you to review anyway. It’s good to have as complete a picture as possible for the new version should something not work as expected, and there are details in the release notes you may not find elsewhere.
Upgrades are a necessary part of any development lifecycle. The chances of having a successful upgrade increases along with the amount of planning and preparation you invest in building a proper upgrade process. If you are planning to upgrade to SQL 2014 you can use this post as a guide to help put together your checklist.
If you haven’t started building up your SQL 2014 migration or upgrade checklist yet, now is the time, and get these items included. They will save you pain, I promise.
32bit 에서 701 에러가 난다.
생명 연장방법
VAS 에서 memtoleave 에 뭘 쓰는지 dump 떠서 모니터링 후 안쓰도록 해본다 (링크드 서버, 확장 dll 등)
-g 옵션으로 늘여본다.
리부팅 자주한다.
마지막은
64bit 로 간다......
갈때 테스트 많이 하세요~ 연동되는시스템이 32bit 만 되요 이런경우가 가끔 있을수 있습니다.
The AdventureWorks database is a fictitious company database that has existed since SQL Server 2005 as a means to show new functionality in each new version released. Because the Books Online TSQL samples leverage this sample database, AdventureWorks has become a vital aspect of learning new features/constructs within SQL Server. What started as a big improvement over the Northwind and Pubs sample databases has evolved into numerous specialized off-shoots highlighting different aspects of the product. There are now multiple versions of AdventureWorks databases for each version of the product, including many updates corresponding to service pack releases. The end result is that it is extremely confusing to find the "correct" version of AdventureWorks that you are searching for. I'll lovingly refer to this as AdventureWorks sprawl and try to lower the barrier to entry for getting started working with the AdventureWorks sample database.
Pre-SQL Server 2005:
AdventureWorks did not exist before SQL Server 2005. In this time, the sample databases Northwind and Pubs were included on the media along with the product and could be installed during the setup process. You can download the Northwind and Pubs sample databases for SQL Server 2000 here.
SQL Server 2005:
In the SQL Server 2005 timeframe, the AdventureWorks sample DB was introduced and also included on the media along with the product. You can install the samples during setup or download them from the following location:
http://msftdbprodsamples.codeplex.com/releases/view/4004
The installers for SQL Server 2005 supply you with the .mdf/.ldf files, but you need to manually attach them to your instance for use. You will see a couple different AdventureWorks databases, each with a slightly different focus. For the majority of people downloading sample databases to learn T-SQL, refer to Books Online examples, etc., the AdventureWorksDB is the first one to start with.
SQL Server 2008:
Beginning with SQL Server 2008, the sample databases are no longer included with the media and must be downloaded and attached as a separate step. The SQL Server 2008 sample databases are packaged slightly different than any other version, where they include a single .exe file that will allow you to install all different versions of the SQL Server 2008 sample databases. They also include a .zip file containing a single .mdf and .ldf for attaching the basic AdventureWorksLT database. I'd suggest unzipping this file and attaching the .mdf/.ldf to get started and using the .exe if you want to dig further into more complex examples.
http://msftdbprodsamples.codeplex.com/releases/view/37109
Step-by-step instructions for using the .exe to install all sample DBs is here.
SQL Server 2008 R2:
Beginning with the 2008 R2 sample databases, a new version was introduced that supported filestream. If you are familiar with SQL Server and trying to learn filestream specifically, you'll want to download the version containing filestream sample schema/code. For the huge bulk of users trying to leverage the sample database to learn the basics and extend into new areas, you will want to download the version without filestream support from the link below:
http://msftdbprodsamples.codeplex.com/releases/view/59211
In this link, you will see numerous versions of the database. To get started quickly, I'd suggest downloading AdventureWorks2008R2-Full Database Backup.zip, unzipping the .bak file to your backup location, and restoring the database through SSMS. The other versions all have different intentions for learning, but again I'd suggest starting with the basic version first before advancing to the other versions.
SQL Server 2012:
Through the evolution of the AdventureWorks DBs over the years, the site hosting the sample databases now includes 13 different links/flavors/versions of the sample database for SQL Server 2012 and can be found here:
http://msftdbprodsamples.codeplex.com/releases/view/55330
As with my suggestion for 2008 R2, I would suggest that you download AdventureWorks2012-Full Database Backup.zip, unzip the .bak file to your backup location, and restore the database through SSMS to get started quickly.
In summary, the AdventureWorks sample databases are outstanding learning tools for understanding examples from Books Online, trying out new constructs, and exploring entire new areas of SQL Server, but wading through the numerous versions and editions can be quite confusing. If you have questions/comments, let me know.
Hope this helps,
Sam Lester (MSFT)