tag:blogger.com,1999:blog-83652218207351094902024-02-07T15:30:46.443-08:00MS SQL SERVER DBA~ a resource for the Microsoft SQL Server.Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.comBlogger64125tag:blogger.com,1999:blog-8365221820735109490.post-42808277795033054922023-08-13T10:56:00.000-07:002023-08-13T10:56:00.505-07:00The Path to Better Investments: Strategies and Tips<p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><h1 style="text-align: justify;"><b><span style="font-family: verdana;">The Path to Better Investments: Strategies and Tips</span></b></h1><p style="text-align: justify;"><span style="font-family: verdana;">Investing is a journey that offers the potential to grow your wealth and achieve your financial goals. However, it's crucial to approach investing with a well-informed and strategic mindset. Whether you're a novice investor or have some experience, understanding the principles of better investing can help you make sound decisions and maximize your returns. In this article, we'll explore key strategies and tips to guide you on your path to better investments.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">1. Define Your Financial Goals:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Before you begin investing, it's essential to clearly define your financial goals. Are you saving for retirement, buying a home, funding education, or seeking long-term wealth accumulation? Different goals require different investment approaches, timelines, and risk tolerance levels. Defining your objectives helps shape your investment strategy and sets the foundation for your decisions.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">2. Understand Risk Tolerance:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Risk tolerance refers to your ability and willingness to endure fluctuations in the value of your investments. Assessing your risk tolerance is crucial because it guides your asset allocation decisions. Generally, risk and potential returns are positively correlated. Be honest with yourself about how comfortable you are with the possibility of market ups and downs.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">3. Diversify Your Portfolio:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Diversification involves spreading your investments across different asset classes, sectors, and geographic regions. This strategy helps reduce the impact of poor performance in any single investment on your overall portfolio. Diversification can help manage risk and increase the potential for stable returns.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">4. Invest for the Long Term:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Investing is not a get-rich-quick scheme. Successful investors often have a long-term perspective. Time in the market allows you to benefit from compounding returns, where your earnings generate additional earnings over time. Stay patient and avoid making knee-jerk reactions to short-term market fluctuations.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">5. Research and Educate Yourself:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Knowledge is a powerful tool in the world of investing. Take the time to research different investment options, understand how markets work, and learn about various investment vehicles. Staying informed empowers you to make informed decisions and avoid potential pitfalls.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">6. Consider Dollar-Cost Averaging:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Dollar-cost averaging involves investing a fixed amount of money at regular intervals, regardless of market conditions. This strategy reduces the impact of market volatility and helps you avoid trying to time the market. Over time, you'll accumulate more shares when prices are low and fewer when prices are high.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">7. Be Mindful of Fees:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Fees can eat into your investment returns over time. Whether you're investing in mutual funds, exchange-traded funds (ETFs), or other vehicles, pay attention to management fees, transaction costs, and other associated expenses. Lower-cost investments can have a significant impact on your long-term results.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">8. Stay Emotionally Disciplined:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Investing can be emotionally challenging, especially during periods of market volatility. Avoid making impulsive decisions driven by fear or greed. Stick to your investment plan, and remember that market fluctuations are a natural part of the investing landscape.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">9. Regularly Review and Adjust:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Your financial situation, goals, and market conditions can change over time. It's important to review your investment portfolio periodically and make adjustments as needed. Rebalancing your portfolio to maintain your desired asset allocation can help ensure that you're staying on track.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">10. Seek Professional Advice:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">If you're uncertain about making investment decisions on your own, consider seeking advice from a qualified financial advisor. A professional can provide personalized guidance based on your unique circumstances, goals, and risk tolerance.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>Conclusion</b>, better investing involves a combination of strategy, discipline, education, and patience. By defining your goals, understanding risk, diversifying your portfolio, and staying informed, you can increase your likelihood of making wise investment decisions that align with your financial aspirations. Remember that investing is a journey, and the key to success lies in informed decision-making and a long-term perspective.</span></p>Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-22045614028672626212023-08-13T10:31:00.001-07:002023-08-13T10:31:18.766-07:00Exploring the Landscape of AI in the Market.<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div style="text-align: center;"><span style="font-family: verdana;"><b>Exploring the Landscape of AI in the Market.</b></span></div></blockquote><p style="text-align: justify;"><span style="font-family: verdana;">Artificial Intelligence (AI) has swiftly transformed from a futuristic concept to a critical component of various industries. As organizations seek to harness the power of AI, a diverse range of AI technologies and platforms have emerged in the market. From machine learning to natural language processing, AI is driving innovation and reshaping the way businesses operate. In this article, we will explore some of the different AI technologies and platforms that are currently making waves in the market.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">1. Machine Learning Platforms:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">Machine learning is a subset of AI that focuses on enabling systems to learn from data and improve their performance over time. Numerous machine learning platforms are available, catering to different skill levels and business needs. Leading platforms include:</span></p><p style="text-align: justify;"><b style="font-family: verdana;">TensorFlow:</b><span style="font-family: verdana;"> Developed by Google, TensorFlow is an open-source machine learning framework known for its flexibility and scalability. It supports a wide range of applications, from computer vision to natural language processing.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">PyTorch:</b><span style="font-family: verdana;"> Created by Facebook, PyTorch is another popular open-source machine learning framework. It is highly praised for its dynamic computation graph and ease of use, making it a favorite among researchers.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>Scikit-learn:</b> A user-friendly machine learning library in Python, Scikit-learn is well-suited for beginners and small-scale projects. It provides a variety of algorithms and tools for tasks like classification, regression, clustering, and more.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>2. Natural Language Processing (NLP) Platforms:</b></span></p><p style="text-align: justify;"><span style="font-family: verdana;">NLP focuses on enabling computers to understand, interpret, and generate human language. NLP platforms have gained significant traction in applications like chatbots, sentiment analysis, and language translation. Notable NLP platforms include:</span></p><p style="text-align: justify;"><b style="font-family: verdana;">BERT (Bidirectional Encoder Representations from Transformers):</b><span style="font-family: verdana;"> Developed by Google, BERT is a pre-trained NLP model that has revolutionized language understanding by capturing context from both directions of a text.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">OpenAI GPT (Generative Pre-trained Transformer):</b><span style="font-family: verdana;"> OpenAI's GPT models are known for their text generation capabilities. GPT-3, the latest iteration, can perform tasks like content creation, code generation, and even answer questions in a conversational manner.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;">3. Computer Vision Platforms:</span></p><p style="text-align: justify;"><b style="font-family: verdana;">Computer vision</b><span style="font-family: verdana;"> is an AI technology that enables machines to interpret and understand visual information from the world. These platforms have applications in areas such as image recognition, object detection, and medical imaging analysis. Prominent computer vision platforms include:</span></p><p style="text-align: justify;"><b style="font-family: verdana;">OpenCV:</b><span style="font-family: verdana;"> Open Source Computer Vision Library (OpenCV) is a widely-used open-source computer vision and machine learning software library. It offers various tools and functions for image and video analysis.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">Microsoft Azure Computer Vision:</b><span style="font-family: verdana;"> This cloud-based service by Microsoft provides capabilities like image recognition, facial recognition, and optical character recognition (OCR), allowing developers to integrate visual AI into their applications.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>4. Robotic Process Automation (RPA) Platforms:</b></span></p><p style="text-align: justify;"><span style="font-family: verdana;">RPA involves the use of software robots to automate repetitive and rule-based tasks. These platforms are particularly useful for streamlining business processes. Leading RPA platforms include:</span></p><p style="text-align: justify;"><b style="font-family: verdana;">UiPath:</b><span style="font-family: verdana;"> UiPath is a popular RPA platform known for its user-friendly interface and extensive automation capabilities. It enables businesses to automate tasks across various applications and systems.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">Automation Anywhere:</b><span style="font-family: verdana;"> Automation Anywhere offers a comprehensive RPA platform that includes features like process discovery, bot creation, and workflow automation.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>5. AI-Powered Analytics Platforms:</b></span></p><p style="text-align: justify;"><span style="font-family: verdana;">AI-powered analytics platforms leverage AI to uncover insights and trends from vast amounts of data. These platforms are instrumental in enhancing decision-making processes. Noteworthy AI-powered analytics platforms include:</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>Tableau:</b> Tableau combines data visualization with AI-driven insights, allowing users to create interactive visualizations and dashboards that help in data-driven decision-making.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">Qlik Sense:</b><span style="font-family: verdana;"> Qlik Sense offers associative data exploration and visualization, augmented with AI capabilities for predictive analytics and data preparation.</span></p><p style="text-align: justify;"><b style="font-family: verdana;"><br /></b></p><p style="text-align: justify;"><b style="font-family: verdana;">6. AI Cloud Services:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">Major cloud providers offer AI services and APIs that enable developers to integrate AI capabilities into their applications without needing to build everything from scratch. These services cover various AI aspects, including image recognition, language processing, and machine learning. Leading AI cloud services include:</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>Amazon AWS AI Services:</b> Amazon Web Services offers a suite of AI services such as Amazon Rekognition for image and video analysis, Amazon Polly for text-to-speech, and Amazon SageMaker for machine learning.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>Microsoft Azure AI Services:</b> Microsoft Azure provides a range of AI services like Azure Cognitive Services for NLP and computer vision, as well as Azure Machine Learning for developing and deploying machine learning models.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;">In <b>Conclusion</b>, the AI market is brimming with diverse technologies and platforms that cater to various needs across industries. From machine learning to natural language processing, computer vision, and more, these AI technologies are driving innovation and reshaping the way businesses operate. As the AI landscape continues to evolve, organizations have an array of tools at their disposal to unlock the potential of artificial intelligence and drive meaningful transformation.</span></p><div style="text-align: justify;"><br /></div>Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-85836511118250408912023-08-13T10:17:00.002-07:002023-08-13T10:17:37.490-07:00 SQL Server 2022 Features in Detail.<p style="text-align: center;"><span style="font-family: verdana;"><b><br /></b></span></p><p style="text-align: center;"><span style="font-family: verdana;"><b>SQL Server 2022 Features in Detail.</b></span></p><p style="text-align: center;"><span style="font-family: verdana;"><b><br /></b></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><b>1. Data Virtualization:</b></span></p><p style="text-align: justify;"><span style="font-family: verdana;">Data virtualization in SQL Server 2022 enables you to access and query data from various sources as if they were part of a single database. This eliminates the need for complex data integration processes and allows for seamless querying of distributed data.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">2. Machine Learning Services Enhancements:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">SQL Server 2022 offers enhanced integration with machine learning frameworks like TensorFlow and PyTorch. This enables you to train and deploy machine learning models directly within the database, bringing AI-driven insights closer to the data.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">3. Improved Security:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">The Data Discovery and Classification capabilities in SQL Server 2022 help identify sensitive data and apply appropriate security measures. Always Encrypted technology ensures that sensitive data remains encrypted in various scenarios.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">4. Big Data Clusters:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">Big data clusters allow you to manage and query large datasets across SQL Server, Hadoop, and Spark clusters. This feature provides powerful capabilities for handling diverse data sources without data movement.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">5. Smart Transaction Log:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">The Smart Transaction Log feature optimizes the management of transaction logs by dynamically adjusting log file sizes. This enhances performance and operational efficiency.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">6. Temporal Tables Enhancements:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">Temporal tables in SQL Server 2022 have been improved with system-versioned temporal tables for automated history retention and application-time temporal tables for more flexible temporal data management.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">7. Query Performance Insights:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">SQL Server 2022's Query Store functionality offers detailed information about query performance over time. This helps database administrators identify and address performance issues effectively.</span></p><p style="text-align: justify;"><b style="font-family: verdana;">8. Containerization and Kubernetes Support:</b></p><p style="text-align: justify;"><span style="font-family: verdana;">SQL Server 2022 provides improved support for containerization and Kubernetes, allowing for easier deployment, scaling, and management of SQL Server instances in cloud-native environments.</span></p>Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-31409478709713014142023-08-13T10:11:00.003-07:002023-08-13T10:11:44.825-07:00Features of SQL Server 2022.<div style="text-align: center;"><span style="text-align: left;"><span style="font-family: verdana;"><b>Features of SQL Server 2022.</b></span></span><span style="font-family: verdana;"> </span></div><p style="text-align: justify;"><span style="font-family: verdana;"><span style="background-color: #f7f7f8; color: #374151; font-size: 16px; white-space-collapse: preserve;">SQL Server 2022, the latest iteration of Microsoft's popular relational database management system, brings a slew of exciting new features and enhancements that cater to the needs of modern data-driven applications. With a focus on performance, security, scalability, and advanced analytics capabilities, SQL Server 2022 is poised to empower developers, database administrators, and data analysts with powerful tools to manage and extract insights from their data. Let's delve into some of the standout features that SQL Server 2022 has to offer.</span></span></p><p style="text-align: justify;"><span style="background-color: #f7f7f8; color: #374151; font-size: 16px; white-space-collapse: preserve;"><span style="font-family: verdana;"><br /></span></span></p><p></p><div style="text-align: justify;"><b style="background-color: #f7f7f8; color: #374151; font-family: verdana; white-space-collapse: preserve;">1. Data Virtualization:</b></div><span style="background-color: #f7f7f8;"><div style="text-align: justify; white-space-collapse: preserve;"><span style="color: #374151; font-family: verdana;">SQL Server 2022 introduces data virtualization, which allows you to query data from multiple sources as if they were part of a single database. This feature facilitates easier access to distributed data and helps eliminate the need for complex ETL processes to combine data from various sources. Data virtualization enables seamless querying of data residing in other databases, data lakes, or even external APIs directly from within SQL Server.</span></div><span style="color: #374151; font-family: verdana;"><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>2. Machine Learning Services Enhancements:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">Building on its previous versions, SQL Server 2022 continues to integrate machine learning capabilities. It offers enhanced integration with popular machine learning frameworks like TensorFlow and PyTorch, making it easier to train and deploy machine learning models directly within the database. This brings the power of AI-driven insights closer to the data.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>3. Improved Security:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">Security remains a top priority for SQL Server, and version 2022 strengthens its security features. The new Data Discovery and Classification capabilities help identify sensitive data and apply appropriate security measures. Enhanced Always Encrypted technology ensures that sensitive data remains encrypted at rest, in motion, and during computation.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>4. Big Data Clusters:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">SQL Server 2022 extends its big data capabilities with improved big data clusters. This feature enables you to deploy, manage, and query large datasets across SQL Server, Hadoop, and Spark clusters. With this enhanced support for big data workloads, organizations can gain insights from diverse data sources without needing to move data between different systems.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>5. Smart Transaction Log:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">The Smart Transaction Log feature optimizes the transaction log management process. It dynamically adjusts the log file size, reducing the need for manual intervention. This helps improve performance and ensures smoother operation, particularly in scenarios with varying workloads.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>6. Temporal Tables Enhancements:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">Temporal tables, introduced in earlier versions, allow you to track and manage changes to data over time. In SQL Server 2022, temporal tables have been enhanced with system-versioned temporal tables, which enable automatic history retention, and application-time temporal tables, which provide more flexible ways to manage temporal data.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>7. Query Performance Insights:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">SQL Server 2022 enhances its query performance insights with integrated query analysis and tuning capabilities. The Query Store functionality provides detailed information about query performance over time, allowing database administrators to identify and address performance bottlenecks more effectively.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>8. Containerization and Kubernetes Support:</b></div></b><div style="text-align: justify; white-space-collapse: preserve;">Keeping up with modern deployment practices, SQL Server 2022 provides better support for containerization and Kubernetes. This enables easier deployment, scaling, and management of SQL Server instances in cloud-native environments.</div><div style="text-align: justify;"><span style="white-space-collapse: preserve;"><br /></span></div><b style="white-space-collapse: preserve;"><div style="text-align: justify;"><b>Conclusion:</b></div></b><span style="white-space-collapse: preserve;"><div style="text-align: justify;">SQL Server 2022 brings a wealth of new features and enhancements that cater to the evolving needs of data-driven applications. From data virtualization to machine learning advancements, improved security, big data clusters, and more, this version empowers organizations to harness the full potential of their data while maintaining high levels of security, scalability, and performance. Whether you're a developer, database administrator, or data analyst, SQL Server 2022 offers tools and capabilities that pave the way for innovative and efficient data management and analysis.</div></span></span></span><p></p><div style="text-align: justify;"><span style="background-color: #f7f7f8; color: #374151; font-family: Söhne, ui-sans-serif, system-ui, -apple-system, "Segoe UI", Roboto, Ubuntu, Cantarell, "Noto Sans", sans-serif, "Helvetica Neue", Arial, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji"; font-size: 16px; white-space-collapse: preserve;"><br /></span></div>Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-23785157414038556932023-08-13T10:02:00.000-07:002023-08-13T10:02:11.191-07:00Earn Passive Income Online in 2023.<div style="text-align: center;"><b><span style="font-family: verdana;">Earn Passive Income Online in 2023.</span></b></div><div style="text-align: center;"><b><span style="font-family: verdana;"><br /></span></b></div><div style="text-align: justify;"><span style="font-family: verdana;">In today's digital age, earning money online has become an increasingly popular option for individuals seeking financial independence and flexibility. With the right strategies and dedication, anyone can tap into the vast opportunities available on the internet. This article presents ten proven strategies that can help you start earning money online and potentially turn it into a sustainable source of income.</span></div><p style="text-align: justify;"><b><span style="font-family: verdana;">Freelancing:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Explore the world of freelancing by offering your skills and services online. Platforms like Upwork, Freelancer, and Fiverr connect freelancers with clients seeking various services, including writing, graphic design, programming, and virtual assistance.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Online Tutoring:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Leverage your expertise in a particular subject by becoming an online tutor. Platforms such as Tutor.com, VIPKid, and Chegg Tutors offer opportunities to teach students worldwide through virtual classrooms.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Affiliate Marketing:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Join affiliate programs and earn commissions by promoting products or services on your website, blog, or social media channels. Amazon Associates, ClickBank, and ShareASale are popular affiliate marketing platforms.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Creating and Selling Digital Products:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Develop digital products like e-books, online courses, stock photos, or software and sell them on platforms such as Gumroad, Teachable, or Etsy. Capitalize on your knowledge and skills to create valuable resources for others.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Dropshipping:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Start an e-commerce business without inventory by setting up a dropshipping store. With platforms like Shopify or WooCommerce and suppliers from AliExpress or Oberlo, you can sell products online and have them shipped directly to customers.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Content Creation and Monetization:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Build a following on platforms like YouTube, Twitch, or TikTok by creating engaging and valuable content. Monetize your channel through ad revenue, sponsored content, brand partnerships, or fan donations.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Online Surveys and Microtasks:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Participate in online surveys or complete microtasks on platforms like Swagbucks, Amazon Mechanical Turk, or Clickworker. While not highly lucrative, these activities can provide some extra income.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Virtual Assistant Services:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Offer administrative, social media management, or customer support services remotely as a virtual assistant. Platforms like Zirtual and Fancy Hands connect virtual assistants with clients who need assistance.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">E-commerce and Amazon FBA:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Launch your own e-commerce store using platforms like Shopify or Amazon FBA (Fulfillment by Amazon). Identify niche markets or unique products to sell and leverage the power of online shopping.</span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Online Investing:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Explore online investment opportunities, such as stocks, cryptocurrencies, or peer-to-peer lending platforms. Educate yourself on investment strategies, manage risks, and gradually grow your wealth.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><b><span style="font-family: verdana;">Conclusion:</span></b></p><p style="text-align: justify;"><span style="font-family: verdana;">Earning money online offers immense potential for individuals willing to put in the effort and learn the necessary skills. Whether it's freelancing, selling products, content creation, or investing, there are numerous paths to success in the digital realm. Start by selecting a strategy that aligns with your interests and strengths, and remember that perseverance and continuous learning are key to achieving long-term financial success online.</span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><span style="font-family: verdana;"><br /></span></p><p style="text-align: justify;"><br /></p>Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-81316299548494635872020-01-17T05:17:00.001-08:002023-06-25T10:27:57.288-07:00SQL Server 2016 Windows Server 2016 Firewall Rule Step-By-Step.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
<br />
SQL Server 2016 Windows Server 2016 Firewall Rule Step-By-Step.<br />
Opening Firewall in SQL Server 2016.<br />
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">Once
the SQL Server is installed on the machine. We must enable the incoming traffic
i.e., from the application to the SQL Server Database. For this purpose, we must
create rule for SQL Server ports</span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"> so that the application can
connect to database & perform the CRUD operations on the tables.<o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;">Now Let’s start Step-by-Step to enable it.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-left: 36pt; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 36.0pt; text-indent: -18pt;">
<!--[if !supportLists]--><span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%;"><span style="mso-list: Ignore;">1.<span style="font: 7pt "Times New Roman";"> </span></span></span><!--[endif]--><span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%;">Start System and Login (As
Administrator);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-left: 36pt; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 36.0pt; text-indent: -18pt;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%; text-indent: -18pt;">2.<span style="font-family: "times new roman"; font-size: 7pt; font-stretch: normal; line-height: normal;"> </span></span><span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%; text-indent: -18pt;">Open <b>Control Panel.</b></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-left: 36pt; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 36.0pt; text-indent: -18pt;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%; text-indent: -18pt;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgyfVWUAvRjO6lLzqDFTRJAarjzLYZb43GyXTjxG4wclwxuJ2z1e0wXiKbE2CMydLcBe4p_oImaYr7vSqgLuMf2_IQjIfbo80anKelBKlmKvZ6UfdLRoeTG5-uNvVztMlUHLSci0HK7O0/s1600/Picture1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="443" data-original-width="940" height="299" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgyfVWUAvRjO6lLzqDFTRJAarjzLYZb43GyXTjxG4wclwxuJ2z1e0wXiKbE2CMydLcBe4p_oImaYr7vSqgLuMf2_IQjIfbo80anKelBKlmKvZ6UfdLRoeTG5-uNvVztMlUHLSci0HK7O0/s640/Picture1.png" width="640" /></a></div>
<div class="MsoNormal" style="line-height: 150%; margin-left: 36pt; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 36.0pt; text-indent: -18pt;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%; text-indent: -18pt;"><b><br /></b></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-left: 36pt; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 36.0pt; text-indent: -18pt;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%; text-indent: -18pt;"><b><br /></b></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f">
<v:stroke joinstyle="miter">
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0">
<v:f eqn="sum @0 1 0">
<v:f eqn="sum 0 0 @1">
<v:f eqn="prod @2 1 2">
<v:f eqn="prod @3 21600 pixelWidth">
<v:f eqn="prod @3 21600 pixelHeight">
<v:f eqn="sum @0 0 1">
<v:f eqn="prod @6 1 2">
<v:f eqn="prod @7 21600 pixelWidth">
<v:f eqn="sum @8 21600 0">
<v:f eqn="prod @7 21600 pixelHeight">
<v:f eqn="sum @10 21600 0">
</v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
<v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f">
<o:lock aspectratio="t" v:ext="edit">
</o:lock></v:path></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_i1034" style="height: 212.4pt; mso-wrap-style: square; visibility: visible; width: 451.2pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">Click on <b>Windows
Firewall;</b></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 12.75pt;">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%;">3. Now click on <b>Advanced
Settings;</b><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_2" o:spid="_x0000_i1033" style="height: 229.2pt; mso-wrap-style: square; visibility: visible; width: 451.2pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdYSK9OcLv-gPkOieDRvbo5DMVk40yWK1CM4rG_hRtUuyAKxCSxaBwA67vEaDfM7_Z3uYHA8HfkWp_u7eVAoTk73BjenIDswfp_8tmoA83NoRki1HA9RmgwDXqmBeuD5ByiQ8YDgJD5kI/s1600/Picture2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="478" data-original-width="940" height="324" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdYSK9OcLv-gPkOieDRvbo5DMVk40yWK1CM4rG_hRtUuyAKxCSxaBwA67vEaDfM7_Z3uYHA8HfkWp_u7eVAoTk73BjenIDswfp_8tmoA83NoRki1HA9RmgwDXqmBeuD5ByiQ8YDgJD5kI/s640/Picture2.png" width="640" /></a></div>
<br />
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p><br /></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p><br /></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 12.75pt;">
<b><span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%;">4. Windows Firewall</span></b><span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 150%;"> console open. If you click on <b>Properties</b>
(right side) – you can disable firewall for all networks. We going to <b>Inbound
Rules</b> (left side) for our rule creation;<o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_3" o:spid="_x0000_i1032" style="height: 333pt; mso-wrap-style: square; visibility: visible; width: 450.6pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-ADQexNemzVAngRVS0mA-Rpa1WPNvq7PnDN6q5HnWeMV1o2M1AYwllnNi3_ISMS9koUWPNI9Rqj1NzKETiN173DLcwKl6_mODdH7FDc5HpSFcTFUlQ4FO6aqE55yH1She8ljH5tlgoZg/s1600/Picture3.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="694" data-original-width="939" height="472" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-ADQexNemzVAngRVS0mA-Rpa1WPNvq7PnDN6q5HnWeMV1o2M1AYwllnNi3_ISMS9koUWPNI9Rqj1NzKETiN173DLcwKl6_mODdH7FDc5HpSFcTFUlQ4FO6aqE55yH1She8ljH5tlgoZg/s640/Picture3.png" width="640" /></a><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 14pt;">After Clicking on the Inbound rules. We will see the
below screen. </span></div>
<br />
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;">Click on New Rule.<o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxKQaC43gJNVJsGYPiX8awC-xQxNtmpdLwFUMLxPNe1PbV3Bz9CWb0Ditf2Fp3EpS4R0BQj3wofbG6vJ5h2pkwguHaXoGpmgJplid0T9wmGufmJgzOIkyL6CdxzkXjzlAUAyGb-1LFmiw/s1600/Picture4.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="695" data-original-width="940" height="472" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxKQaC43gJNVJsGYPiX8awC-xQxNtmpdLwFUMLxPNe1PbV3Bz9CWb0Ditf2Fp3EpS4R0BQj3wofbG6vJ5h2pkwguHaXoGpmgJplid0T9wmGufmJgzOIkyL6CdxzkXjzlAUAyGb-1LFmiw/s640/Picture4.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_4" o:spid="_x0000_i1031" style="height: 333.6pt; mso-wrap-style: square; visibility: visible; width: 451.2pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">6. Rule creation process
begin. In my case, I create rule for<b> Inbound TCP ports. </b>For <b>Rule Type</b>
select option <b>Port</b> and click <b>Next</b>;</span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p><br /></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZQ7OUB82xRyGtSrB8UM9hvgElxj4t18Nqiqmmnq0hkHEMQQdWPjbZR5wmbzpXX2qIgrFtXEkj4l-e_bWBbTHZPlmD0LCw3ZS2qSptcQZinUzOm4muWMZgTxokBy1hJissZ_BUv91RUho/s1600/Picture5.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="684" data-original-width="939" height="466" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZQ7OUB82xRyGtSrB8UM9hvgElxj4t18Nqiqmmnq0hkHEMQQdWPjbZR5wmbzpXX2qIgrFtXEkj4l-e_bWBbTHZPlmD0LCw3ZS2qSptcQZinUzOm4muWMZgTxokBy1hJissZ_BUv91RUho/s640/Picture5.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p><br /></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p><br /></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_5" o:spid="_x0000_i1030" style="height: 328.2pt; mso-wrap-style: square; visibility: visible; width: 450.6pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">7. For <b>Protocol and
Ports</b> select option <b>TCP</b> and <b>Special Local Ports. </b>SQL TCP
ports (1435). After entering ports click <b>Next</b>;</span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn_r7qrjrArUkGK_dpTOjb6A_gU98PEphWvYXmXMX5mTdnyFaJrF0tnIZrt0Iq5i1A4VCxyYEkMq2wv2_bDCa_D-aaB1R5jgT4xyI-HbcGcWF4YGL4FiygEIcOoURQhbfVhxZrBKneAm8/s1600/Picture6.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="685" data-original-width="940" height="466" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn_r7qrjrArUkGK_dpTOjb6A_gU98PEphWvYXmXMX5mTdnyFaJrF0tnIZrt0Iq5i1A4VCxyYEkMq2wv2_bDCa_D-aaB1R5jgT4xyI-HbcGcWF4YGL4FiygEIcOoURQhbfVhxZrBKneAm8/s640/Picture6.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_6" o:spid="_x0000_i1029" style="height: 328.8pt; mso-wrap-style: square; visibility: visible; width: 451.2pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image006.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">8. For <b>Action</b> (in
this case leave by default <b>Allow the connection</b>). Click <b>Next</b>;</span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiATgJDses9KX6Mp3gdJ4wtTXshTPF5dRtvOeY4uAZ9z1XXXfy6FjYorekh-jTd7fsJEUzrmZs4Er1jYc46-Kxb9ETUl9ruJtl_yWYBvJaYAZnzvHSPrZb9azSQZgjTaraFgSkQIvR3_0A/s1600/Picture7.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="689" data-original-width="939" height="468" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiATgJDses9KX6Mp3gdJ4wtTXshTPF5dRtvOeY4uAZ9z1XXXfy6FjYorekh-jTd7fsJEUzrmZs4Er1jYc46-Kxb9ETUl9ruJtl_yWYBvJaYAZnzvHSPrZb9azSQZgjTaraFgSkQIvR3_0A/s640/Picture7.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_8" o:spid="_x0000_i1028" style="height: 330.6pt; mso-wrap-style: square; visibility: visible; width: 450.6pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image007.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">9. For <b>Profile</b>,
leave by default, click <b>Next;</b></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8q3ED9xSVVF73BrqjMVilOkywyXifJp5UTlXG9yMW4yreiHyYjR3X8G0kOUpFNl5evoZX2AKJn8ips64RTSO1q2q_xyvBVsVn9YrAOFIeCEjcLT-2tTLvnCriULJlHHUp-5tqAYtxQfU/s1600/Picture8.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="685" data-original-width="939" height="466" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8q3ED9xSVVF73BrqjMVilOkywyXifJp5UTlXG9yMW4yreiHyYjR3X8G0kOUpFNl5evoZX2AKJn8ips64RTSO1q2q_xyvBVsVn9YrAOFIeCEjcLT-2tTLvnCriULJlHHUp-5tqAYtxQfU/s640/Picture8.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><b><br /></b></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_9" o:spid="_x0000_i1027" style="height: 328.8pt; mso-wrap-style: square; visibility: visible; width: 450.6pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image008.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;">10. Enter name for your
rule (as example <b>MSSQL_DB</b>), so you can easily find it in future. Click <b>Finish;</b><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7fiK0reYddsWTcuu7m48sZ0nKoGZn9Ru0DigR1jHCy5riAbU0dpBPe4xM32YWxZhtERz__tiSmYVO2JDc0X1uQWGQBO1Tg21UoGOu25SmxL4tNfCwo7ViONZPBeDhB5WZHCqKmL22WoA/s1600/Picture9.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="689" data-original-width="939" height="467" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7fiK0reYddsWTcuu7m48sZ0nKoGZn9Ru0DigR1jHCy5riAbU0dpBPe4xM32YWxZhtERz__tiSmYVO2JDc0X1uQWGQBO1Tg21UoGOu25SmxL4tNfCwo7ViONZPBeDhB5WZHCqKmL22WoA/s640/Picture9.png" width="640" /></a></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><b><br /></b></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="color: #222222; font-size: 14pt; line-height: 107%;"><b><br /></b></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_10" o:spid="_x0000_i1026" style="height: 330.6pt; mso-wrap-style: square; visibility: visible; width: 450.6pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image009.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;">Now We can see the Firewall rule which has been created
just now in the Inbound Rules.<o:p></o:p></span></div>
<div class="MsoNormal">
<span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><v:shape id="Picture_x0020_12" o:spid="_x0000_i1025" style="height: 331.2pt; mso-wrap-style: square; visibility: visible; width: 451.2pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/sandkuma/AppData/Local/Temp/msohtmlclip1/01/clip_image010.png">
</v:imagedata></v:shape></span><span face=""verdana" , sans-serif" style="font-size: 14pt; line-height: 107%;"><o:p></o:p></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-3KhNJnNMwU4SsHOKsSzjWFKSPAhMcKJmBPO_K3lUKAjCXuKyLIF-7MbfkeU6W7GHpYnwfL5_LUYzsC40m0JJVgbch6T0S2B0M5jzPmdJ2rKP8atb6LjPIG_MttqazR0L5DlkmzSG-cg/s1600/Picture10.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="690" data-original-width="940" height="468" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-3KhNJnNMwU4SsHOKsSzjWFKSPAhMcKJmBPO_K3lUKAjCXuKyLIF-7MbfkeU6W7GHpYnwfL5_LUYzsC40m0JJVgbch6T0S2B0M5jzPmdJ2rKP8atb6LjPIG_MttqazR0L5DlkmzSG-cg/s640/Picture10.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEyZJbQOKVep7ykC9WhmdZGv95KVR-icXRdaRbVQdFlIcksDBB_MRjAix9F3-7TGhdz4rqBhs-KRBzQeC6XIvtyrWRbiwHbkgClkVW67dyg55Gu-AHeJ8nhJ8nHFZJ_363tFJnpVP_8QI/s1600/Earnbounty_640_290_0208.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="290" data-original-width="640" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEyZJbQOKVep7ykC9WhmdZGv95KVR-icXRdaRbVQdFlIcksDBB_MRjAix9F3-7TGhdz4rqBhs-KRBzQeC6XIvtyrWRbiwHbkgClkVW67dyg55Gu-AHeJ8nhJ8nHFZJ_363tFJnpVP_8QI/s400/Earnbounty_640_290_0208.jpg" width="400" /></a></div>
<br /></div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-77824097624940756112020-01-09T09:03:00.006-08:002020-01-17T05:18:24.230-08:00Video's Links of SQL Server.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<br />
<div class="post-body entry-content" id="post-body-7782409762494075611" itemprop="description articleBody">
<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: left;">
<b><span style="font-family: "verdana" , sans-serif;">YouTube Video's Links of SQL Server.</span></b></div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Monitoring of Database Objects for Space Issues</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/ywfEeHnr8sw">https://youtu.be/ywfEeHnr8sw</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Increase in the Growth of the Log File | SQL Server</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/eWvfwqPB6to">https://youtu.be/eWvfwqPB6to</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>To find the Active Running Transactions on SQL Server.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/6jPl1jKRfvU">https://youtu.be/6jPl1jKRfvU</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Shrinking a Log File of a Database in Always on Availability.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/VKlbiz-HNbQ">https://youtu.be/VKlbiz-HNbQ</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Self Introduction and Responsibilities of DBA.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/Td-8nQ6cRe4">https://youtu.be/Td-8nQ6cRe4</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Re-Indexing of Database.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/0Dd2ylJBjG8">https://youtu.be/0Dd2ylJBjG8</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Shrinking of LDF and MDF Files on Drives</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/LtbSaiHu6Ho">https://youtu.be/LtbSaiHu6Ho</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Check SQL Server Services</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/Zg1PPBHVM5k">https://youtu.be/Zg1PPBHVM5k</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Validate Database Backup</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/cztc8PGbdZM">https://youtu.be/cztc8PGbdZM</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos </span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">of my channel</span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Daily Job Monitoring in SQL Server.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/v5oQDvPqsQU">https://youtu.be/v5oQDvPqsQU</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Creating_Scheduling DB Jobs in SQL Server.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/cPMP7-ugAYs">https://youtu.be/cPMP7-ugAYs</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos </span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">of my channel</span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;">...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Check Status Availability Database</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/T9yMb1m5hPU">https://youtu.be/T9yMb1m5hPU</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Backup Types in SQL Server</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/jESgGFNMUlo">https://youtu.be/jESgGFNMUlo</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Creating an Alert in SQL Server</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/DSQqImwT7-8">https://youtu.be/DSQqImwT7-8</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Restoring a Database in SQL Server</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/EluJIqEYsn8">https://youtu.be/EluJIqEYsn8</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Instant File Initialisation in SQL SERVER.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/oP1baIHvEXY">https://youtu.be/oP1baIHvEXY</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Creating an Operator - SQL Server.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/vZGOfZezF6o">https://youtu.be/vZGOfZezF6o</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Configuring Database Mail in SQL Server.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/KCOpvJWCqUY">https://youtu.be/KCOpvJWCqUY</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Recovery Models in SQL Server.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/14L42pUoQJM">https://youtu.be/14L42pUoQJM</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Connecting to SQL Server Instance.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/aWVzdHqqTP4">https://youtu.be/aWVzdHqqTP4</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>Self Introduction and Responsibilities of a DBA</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/tlaQRTGp3So">https://youtu.be/tlaQRTGp3So</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
</div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>SQL Server 2017 Windows Server 2012 Firewall Rule Step-By-Step</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/BP2DAytkUK8">https://youtu.be/BP2DAytkUK8</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>SQL Server 2017 Installation Step By Step.</b></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/ZTuz8aKRPBU">https://youtu.be/ZTuz8aKRPBU</a></span></div>
<div>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>SQL Server 2017 Windows Server 2012 Firewall Rule Step-By-Step</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/BP2DAytkUK8">https://youtu.be/BP2DAytkUK8</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Please Like, Subscribe, share the videos of my channel...!!!</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span><span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><b>SQL Server 2017 Installation Step By Step.</b></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><a href="https://youtu.be/ZTuz8aKRPBU">https://youtu.be/ZTuz8aKRPBU</a></span><br />
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: "verdana" , sans-serif; font-size: xx-small;">Best Hotel to Stay in Bangalore....!!!</span></div>
</div>
</div>
</div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-32592725723186628812017-04-17T00:36:00.003-07:002023-06-26T14:34:16.751-07:00Shrinking a SQL Server Log File with Database in Always ON Availability.<div dir="ltr" style="text-align: left;" trbidi="on">
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">Shrinking a <b>SQL Server</b> Log File with Database in Always ON Availability.<br /><br /></span>
<span face=""verdana" , sans-serif"> Once we had a situation in our production server that the database which is in Always ON Availability group has been grown to the maximum size.</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif"> Many of them say that we can't shrink the log file as the database is in Always ON and we should take the downtime to perform the operation. But we shrink-ed the database log file to 5 GB from 180 GB.</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif"><b>Steps are as below:</b></span><br />
<br />
<span face=""verdana" , sans-serif">--To Check the Number of pages allocated by the LogFile.</span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC LOGINFO</b></span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--To check the file sixe</span><br />
<b><span face=""verdana" , sans-serif" style="color: #674ea7;">sp_helpdb [SQLPantry]</span></b><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--To check the Log file Size</span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC SQLPERF(LOGSPACE)</b></span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">The above statement will show the logspace of all db at present situation.</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--To check the Log_reuse_wait description of a log file.</span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>select log_reuse_wait_desc,* from sys.databases</b></span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b><br /></b></span>
<span face=""verdana" , sans-serif">The above statement will show the status of the log whether it has to be backuped i.e., LogBackup, Nothing, Active Transaction. </span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">When ever there is status as Active Transaction then we have to wait till the transaction has completed and start Backup Log and shrink the log file.</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--Take a log backup.</span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>Backup log [SQLPantry] to disk='D:\Backups\OnyxDevDaily\1.trn'</b></span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">The above statement will be used to backup the log first and then start the shrinking of log file.</span><br />
<span face=""verdana" , sans-serif">Atleast 3 or 4 time we have to take the log backups and start the shrink operation on it.</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--To shirnk the log file after taking the backup. and run back to back.</span><br />
<span face=""verdana" , sans-serif">--Log File has grown to the Szie of 3008457 GB</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC SHRINKFILE(2,300000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,200000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,100000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,90000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,80000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,70000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,60000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,50000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,40000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,30000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,20000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,10000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>DBCC
SHRINKFILE(2,5000)<o:p></o:p></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b><br /></b></span></div>
<div style="margin: 0in 0in 0.0001pt;">
<span face=""verdana" , sans-serif">The above statements are used to shrink the log file if the log has been increase to huge size then that has to be changed according to the requirement and perform it. </span></div>
<br />
<div class="MsoNormal">
</div>
<span face=""verdana" , sans-serif"><br /></span>
<span face=""verdana" , sans-serif">--To check the open transactions on the Database</span><br />
<span face=""verdana" , sans-serif"><br /></span>
<br />
<div class="MsoNormal">
<span face=""verdana" , sans-serif"><span style="line-height: 107%;"><span style="color: #674ea7;"><b>DBCC
OPENTRAN()</b></span></span></span><br />
<span face=""verdana" , sans-serif"><span style="line-height: 107%;"><span style="color: #674ea7;"><b><br /></b></span></span></span>
<span face=""verdana" , sans-serif"><span style="line-height: 107%;"><span style="color: #674ea7;"><b>Please check the new videos: <div class="separator" style="clear: both; text-align: center;"><iframe class="BLOG_video_class" allowfullscreen="" youtube-src-id="VKlbiz-HNbQ" width="400" height="322" src="https://www.youtube.com/embed/VKlbiz-HNbQ"></iframe></div></b></span></span><o:p></o:p></span><br />
<span face=""verdana" , sans-serif"><span style="line-height: 107%;"><span style="color: #674ea7;"><b><br /></b></span></span></span>
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>Shrinking of LDF and MDF Files on Drives</b></span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>https://youtu.be/LtbSaiHu6Ho</b></span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b><br /></b></span>
<span face=""verdana" , sans-serif"><span style="line-height: 107%;"><b><span face=""verdana" , sans-serif" style="color: #674ea7;"></span></b></span></span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b>Please Like, Subscribe, share the video for more videos...!!!</b></span><br />
<span face=""verdana" , sans-serif" style="color: #674ea7;"><b><br /></b></span></div>
<div>
<span face=""verdana" , sans-serif"><br /></span></div>
<span face=""verdana" , sans-serif"><br /></span>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com1tag:blogger.com,1999:blog-8365221820735109490.post-8221650916451870062016-07-25T03:40:00.005-07:002020-01-17T05:19:22.713-08:00Increase in the Growth of the SQL Server Log File.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
There are many reasons which cause the SQL Server Log file to grow to unbelievable size i.e., the space available in the Disk.<br />
<br />
<br />
1. whenever we have not scheduled a Log_backup on the server the log file size grows bigger and occupies the disk on the specific drive.<br />
2. If we have any repplication setup. Then we will be have log file growth.<br />
3. Due to long running transaction also that causes the Log file grows.<br />
<br />
<br />
All the transactions that are performed on the SQL are done at the log files, after a commit transaction i.e.., from code or the SQL Server check point the data is updated to the Data file of the database.<br />
<br />
To Check the Growth of the Database Log files.<br />
<br />
<b>DBCC SQLPERF(LOGSPACE)</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAZ_26zBtjyNhuICTInNA088F_y0jzK-_Z3SCHkY2kBTyCNlU7MQgjacIrpBPfHzGtypSU3R22tj_2LIlJHDS0LhAXfKaBdaSnxZ7gTsbXQO1cCar1iA9jSmfSiif31wbrp0hjZfYx-ug/s1600/LogFileSize.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="283" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAZ_26zBtjyNhuICTInNA088F_y0jzK-_Z3SCHkY2kBTyCNlU7MQgjacIrpBPfHzGtypSU3R22tj_2LIlJHDS0LhAXfKaBdaSnxZ7gTsbXQO1cCar1iA9jSmfSiif31wbrp0hjZfYx-ug/s400/LogFileSize.PNG" width="400" /></a></div>
<br />
The Log Size (MB) column shows the size of the log files<br />
The Log Space Used(%) column shows how much of the log space is used by the SQL Server Log Files.<br />
<br />
Example:<br />
<br />
In some environment we will be able to see the below<br />
<br />
<b>Log Size (MB): 43562.19</b><br />
<b>Log Space Used: 1000.45</b><br />
<br />
When ever we take the Standard reports on the SQL Server we can see the following.<br />
<b>Total Space Use: 43562.19 MB</b><br />
<b>Data File Space Use: 847.88 MB</b><br />
<b>Transaction LogUse: 42714.31 MB</b><br />
<br />
By the above figures we can see that only 188 MB is used by the log File. The The 42 GB space is empty. So we can release the space so that it can be accommodated to the disk. It can be used by the SQL Server for usage to store the data on the disk.<br />
<br />
<br />
<br /></div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-11547253657996091712016-07-25T03:04:00.002-07:002020-01-17T05:20:06.629-08:00SQL Server Long Running Transaction<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b>SELECT DISTINCT TOP 10</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>t.TEXT QueryName,</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>s.execution_count AS ExecutionCount,</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>s.max_elapsed_time AS MaxElapsedTime,</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>s.creation_time AS LogCreatedOn,</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>FROM sys.dm_exec_query_stats s</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>ORDER BY</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>s.max_elapsed_time DESC</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>GO</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgC-4dukb6gUb9ysU2XFuFdHd-wb8FPTovDhRImjgtWFAo5S9q0Jr3t8h7tpN4ddyYHaJiNmArTo2h4rYk67QoJZSehPTdlQdbFWUmJWlCddijxhalJJPgQCWZdeO3yYBpU3ycRXcFa8dk/s1600/Long+running+Query.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgC-4dukb6gUb9ysU2XFuFdHd-wb8FPTovDhRImjgtWFAo5S9q0Jr3t8h7tpN4ddyYHaJiNmArTo2h4rYk67QoJZSehPTdlQdbFWUmJWlCddijxhalJJPgQCWZdeO3yYBpU3ycRXcFa8dk/s320/Long+running+Query.PNG" width="320" /></a></div>
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<span style="font-family: "trebuchet ms" , sans-serif;"><b>To find the Active Running Transactions on SQL Server.</b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b><a href="https://youtu.be/6jPl1jKRfvU">https://youtu.be/6jPl1jKRfvU</a></b></span><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b><br /></b></span>
<b><span style="font-family: "trebuchet ms" , sans-serif;"></span></b><br />
<span style="font-family: "trebuchet ms" , sans-serif;"><b>Please Like, Subscribe, share the video for more videos...!!!</b></span><br />
<div>
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-65493288237191275522016-07-25T03:01:00.002-07:002020-01-17T05:20:48.102-08:00SQL Server Query - To find the Active Running Transactions on SQL Server.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The below query shows the output of the transactions that are actively running on the Server.<br />
<br />
<br />
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">SELECT sqltext.TEXT,</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">dm.session_id,</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">dm.status,</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">dm.command,</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">dm.cpu_time,</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">dm.total_elapsed_time</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">FROM sys.dm_exec_requests dm</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;">CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext</span></b></div>
<div style="text-align: left;">
<b><span style="font-family: "trebuchet ms" , sans-serif;"><br /></span></b></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbBf9WD2Pb-Fnu9Xs7WW6RbM-pOW8lNJLymv99Eqhkkz8cYU2wxpOL_QqkPsv9vf7sepEQtgSev5gVl1oBIYG9YU2xdM7iIo_4ArcmnFq0WvPfqfkYPQlfDGfnq3uZ0-HZJ6MDU_v7lkM/s1600/Running_Transactions.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbBf9WD2Pb-Fnu9Xs7WW6RbM-pOW8lNJLymv99Eqhkkz8cYU2wxpOL_QqkPsv9vf7sepEQtgSev5gVl1oBIYG9YU2xdM7iIo_4ArcmnFq0WvPfqfkYPQlfDGfnq3uZ0-HZJ6MDU_v7lkM/s320/Running_Transactions.PNG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
When we run the above query it shows the output what are the transactions that are running on the server. And it varies from time to time. As we refresh the query we will find the new query's executing on the server. This can be experienced when we run the query on the Production environment. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The same can be implemented on the stand-alone system i..e., personal computer where we schedule some jobs with SQL Script that inserts some information and some query's to retrieved the data.</div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-24559756009784233182016-03-31T06:48:00.001-07:002020-01-17T05:21:03.820-08:00SQL Server Always On High Availability Video<div dir="ltr" style="text-align: left;" trbidi="on">
<body>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/QJSo23eu0T4/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/QJSo23eu0T4?feature=player_embedded" width="320"></iframe></div>
</div>
<script type="text/javascript">
var vglnk = {key: '2c4a33e0c9ed6b923d0b89f5a6a585af'};
(function(d, t) {
var s = d.createElement(t);
s.type = 'text/javascript';
s.async = true;
s.src = '//cdn.viglink.com/api/vglnk.js';
var r = d.getElementsByTagName(t)[0];
r.parentNode.insertBefore(s, r);
}(document, 'script'));
</script>
</body></div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-60918495345582082922015-03-28T01:23:00.004-07:002020-01-17T05:21:40.710-08:00SSRS Report Layout Types <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;">Report layout can be boiled down to a few, simple design patterns. These simple styles also can be combined with others to form composite reports and more advanced layout to visualize data. This section offers a quick review of the report layout types, followed by some examples. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> </span></div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;">Tabular Reports </span></b></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Tabular reports have been around for thousands of years. Well, they may not have been reports in the modern sense, but when you think about it, some common reports are really little more than “ lists of stuff ” organized into rows and columns. Ever since early merchants began trading seashells or precious gems for beaver pelts or goat cheese, someone was recording the transaction in some kind of list, be it on papyrus, stone tablets, or a tablet PC. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Ever since VisiCalc, the predecessor to Lotus 123 and Microsoft Excel, was released in 1981, the tabular spreadsheet format has become the way many computer users are accustomed to viewing business data. For decades, the only printed reports available from mainframe and midrange computer systems were green - bar reports printed on pin- fed, fan - folded “ greenbar ” paper in classic spreadsheet style. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> In Reporting Services for SQL Server 2000 and 2005, Tabular reports were defined using the Table data range item. In addition to the repeating detail rows, data can be grouped on various fields, and each group can have headers, footers, breaks, and subtotals. Tabular reports have a finite number of columns, typically representing the fields in a database table. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;"> Matrix Reports </span></b></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Tabular reports can be fine for logging detailed transactions and lists, but business reporting is often about summarizing information for analysis and to provide context to all the numbers and listed items. This is often best done by rolling up the details along groups and hierarchies and then viewing the aggregate totals, rather than the details. A matrix, cross - tab, or pivot report aggregates data along the X - axis and Y - axis of a grid to form a summarized table. The most unique characteristic of a matrix is that columns are not static but are based on grouped values. Both rows and column groups may be multilevel hierarchies, and there may be an infinite number of grouped members on rows and columns. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> A matrix is most useful for viewing aggregated values along two different dimensional hierarchies, such as time and geography. For example, a product sales summary report might show aggregated sales with years and months on the columns axis and then the customers ’ countries and regions along the rows axis. At the intersection of each member along each axis, a cell displays the summarized product sales for that time and geography. For example, a single cell might represent the total sales for April of 2008 in Berlin, Germany. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;"> List Reports </span></b></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> A List report consists of a single, rectangular detail area that repeats for every record or group value in the underlying dataset. If you think about it, a list is a simplified table of sorts, but it has no headers with only one column and only one detail row. The main purpose of the list data region is to contain other related data regions and report items, and to repeat them for a group of values. A chart, table, matrix, and any combination of textboxes or images can be repeated as a group for every record or distinct group value returned by a query. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Because the table, list, and matrix had similar characteristics, all three have been combined into one designer object in the 2008 product, called the tablix . The capabilities and unique behaviors of each of these three reporting paradigms are made possible by enabling different group types and properties. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;"> Chart Reports </span></b></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Behold, the mighty chart — no longer an exception, but very much the rule for expressing aggregated data values for comparison and trending. Column and line charts have become a natural medium to visualize a series of data in a meaningful and intuitive way. We ’ ve grown accustomed to seeing charts on the stock page of the newspaper, and on the home page of our news portal site, showing gas prices and housing market data. And, of course, we expect to see charts in the executive board room, used to explain the latest widget sales trends. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> The nice thing about charts is that they provide visual context for a lot of different kinds of data. When used appropriately, the right chart types can tell a complex and important story with very little explanation required. Your challenge is to choose the right type of charts to visualize data in the most meaningful way for your users. In addition to the typical set of column, bar, line, point, and pie charts, you have a huge array of specialized chart types at your disposal. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;"> Gauge Reports and Dashboards </span></b></div>
<div style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif;"><br /></span></b></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> The term dashboard reports gets tossed around loosely these days, often without much qualification. Although different people may not be able to clearly define exactly what a Dashboard report is or is not, I think the essential concept is quite clear. Think of the dashboard in your car. Its purpose is not to provide deep and detailed analysis of your car ’ s performance. If it did, there would be more road accidents because of distracted drivers. No, the purpose of a dashboard is to provide a quick, at - a - glance status of important metrics. All you really need to know is if your speed is in an acceptable range, whether the engine is revving too little or too much, if the oil and water are too hot or too cold, and whether you have enough gas to get to the office for that meeting in 10 minutes (or if you’ re going to have to make your presentation to the CEO by phone with your hand cautiously cupped over your cell phone at the filling station). </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><b> Dashboard reports</b> are the same — important information available at a glance. Everyone knows how to read a simple gauge, so why not use the same gauge visualizations as those we ’ re accustomed to using in the car or on the machines on the production floor? Actually, there are several tangible gauge- type metaphors that are very appropriate in business, and the great thing about using these components is that when a business user sees a thermometer, VU meter, dial, or partially full cylinder, they immediately understand its meaning. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><b>Composite Reports</b> The layout types discussed above are more than a set of finite report design options. These report items and data range objects can be used as building blocks to assemble more complex and compelling report solutions.</span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;">Once you have the basics down, you can combine the report design and layout elements to create more advanced and compelling reports. Different report items and data ranges can be embedded into a data range to repeat the data visualization within the scope of a group row or column.</span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> <b>Page Layout </b> Let’s discuss some important information you need to understand before you move on. These examples were created on a computer configured with US/English regional settings. As a result, all of the scaling units are set to inches. If your computer is configured for another culture or regional setting, your environment may use metric units. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> It’s also important to understand how a report fits onto a page. The report content fits onto a design element called the body . The report defines the page for printing and displaying purposes with associated margins. The relationship between these two design elements will be discussed shortly. </span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"> Different scaling units — such as inches, points, pixels, and metric scale units — can be used for the report, body, and margins and control size measurements. The Designer will automatically use either inches ( “ in ” ) or centimeters (“ cm ” ), depending on the current locale setting in Windows. </span></div>
</div>
<div style="text-align: justify;">
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-72018140190773344492015-03-28T01:07:00.005-07:002020-01-17T05:22:09.667-08:00SQL Server Reporting Services Tools.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0in; text-align: justify;">
<br /></div>
<div style="margin-bottom: 0in; text-align: justify;">
<span style="font-family: "verdana" , sans-serif;">Reporting Services Tools Reporting
Services supports the full reporting life cycle. This support is
provided through a collection of tools that come with Reporting
Services, as identified in the following table.</span></div>
<div style="margin-bottom: 0in; text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<table border="1" bordercolor="#000000" cellpadding="4" cellspacing="0" style="text-align: justify; width: 100%px;">
<colgroup><col width="85*"></col>
<col width="85*"></col>
<col width="85*"></col>
</colgroup><tbody>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;"><b>Authoring </b>
</span></td>
<td width="33%"><b><span style="font-family: "verdana" , sans-serif;">Management</span></b></td>
<td width="33%"><b><span style="font-family: "verdana" , sans-serif;">Delivery</span></b></td>
</tr>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Report Designer</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Report Manager</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;"> Report Manager</span></td>
</tr>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Report Builder1.0</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;">SharePoint reports library</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;">HTML Viewer</span></td>
</tr>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Report Builder 2.0</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Reporting Services Configuration Manager</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;">SharePoint libraries and web parts</span></td>
</tr>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;"><br />
</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;"><br />
</span></td>
<td width="33%"><span style="font-family: "verdana" , sans-serif;"><br />
</span></td>
</tr>
<tr valign="TOP">
<td width="33%"><span style="font-family: "verdana" , sans-serif;">Third – party authoring tools</span></td>
<td width="33%"><div style="margin-bottom: 0in;">
<span style="font-family: "verdana" , sans-serif;"> SQL Server management applications
</span></div>
<span style="font-family: "verdana" , sans-serif;">Command - line utilities</span></td>
<td width="33%"><div style="margin-bottom: 0in;">
<span style="font-family: "verdana" , sans-serif;">Report Viewer controls</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: "verdana" , sans-serif;"><br />
</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: "verdana" , sans-serif;">Reporting Services Web service</span></div>
<span style="font-family: "verdana" , sans-serif;">Subscriptions</span></td>
</tr>
</tbody></table>
<div style="margin-bottom: 0in; text-align: justify;">
<span style="font-family: "verdana" , sans-serif;"><br />
</span></div>
<br />
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
</div>
<br />
<div style="margin-bottom: 0in; text-align: justify;">
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-63463764756110913152015-03-28T00:55:00.000-07:002020-01-17T05:22:45.983-08:00 The Reporting Life Cycle<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The reporting life cycle is often described as a process consisting of three sequential phases. A report is first designed and developed in the authoring phase, made accessible to end- users in the management phase, and then placed in the hands of end - users in the delivery phase.<br />
<br />
<div style="text-align: center;">
Authoring</div>
<div style="text-align: center;">
• End-User</div>
<div style="text-align: center;">
• Report Specialist</div>
<div style="text-align: center;">
|</div>
<div style="text-align: center;">
Management</div>
<div style="text-align: center;">
• Content </div>
<div style="text-align: center;">
• System</div>
<div style="text-align: center;">
|</div>
<div style="text-align: center;">
Delivery</div>
<div style="text-align: center;">
• On-Demand (Pull) </div>
<div style="text-align: center;">
• Subscription (Push)</div>
<br />
<b> Authoring </b><br />
<div style="text-align: justify;">
The authoring phase of the reporting life cycle starts with the gathering of requirements through formal and informal processes. These requirements then drive the design of queries that provide data for the report. Data is integrated with charts, tables, matrices, or other presentation elements to form the basic report. Formatting and layout adjustments are then applied to produce a draft report that is validated for accuracy and consistency with the requirements before being published to a centralized management system in preparation for end- user consumption. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Report authoring is handled by two general classes of workers: </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b> End - User Authors</b> — End - user authors develop reports as a secondary part of their job. These folks typically belong to the non - IT part of an organization and tend to require less technical, more user - friendly report authoring tools. These tools present data in a manner that is easy to interpret and incorporate into the report design and make report layout and formatting a relatively simple task. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Reporting Specialists</b> — Reporting specialists, on the other hand, are focused on report development as a primary part of their job. These folks often reside within the IT department. Reporting specialists demand precise control over query and report design. Their authoring tools tend to be more technical, providing access to the complete array of features available through the reporting system. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Of course, not every report author falls neatly into one of these two buckets. The end - user author and the reporting specialist represent two ends of a spectrum, with many authors leaning toward one end or the other. A variety of report development tools are needed to address the full range of needs along this spectrum. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<div>
<b> Management</b> </div>
<div>
<br /></div>
<div>
In the management phase of the reporting life cycle, published reports are organized, secured, and configured for end - user access. Resources employed by multiple reports and specialized features, such as subscription delivery and caching, are configured. These activities are collectively referred to as content management and are often handled to some degree by both authors and administrators. </div>
<div>
<br /></div>
<div>
The report management system itself requires configuration and ongoing maintenance to ensure its continued operation. System management activities are often the exclusive domain of administrators. </div>
<div>
<b> Delivery </b></div>
<div>
<b><br /></b></div>
<div>
Once deployed and configured, a report is ready for end- user consumption, in the delivery phase of the reporting life cycle. End - users may view reports on demand or may request that reports be delivered to them on a predefined schedule. These are referred to as the pull and push methods of report delivery, respectively. The key to successful report delivery is flexibility. </div>
<div>
<br /></div>
</div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-55695719883943468662015-03-28T00:46:00.003-07:002020-01-17T05:23:15.255-08:00SQL Server Reporting Service Modes<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
(SSRS) Reporting Services runs in one of two modes: Native or SharePoint Integrated. In the Native mode, Reporting Services manages its content using its own internal, or “ native, ” functionality. This is the traditional mode under which Reporting Services instances have been run.<br />
<br />
Reporting Services deployments using Enterprise, Developer, Enterprise Evaluation, or Standard editions are capable of running in the SharePoint Integrated mode. In this mode, content management is handled through SharePoint. Native content management interfaces, such as Report Manager, are no longer accessible.<br />
<br />
The SharePoint Integrated mode is a very appealing option for many organizations that want to leverage SharePoint as their enterprise content - management solution; however, there are some limitations, such as the lack of support for linked reports in this mode.<br />
<br />
For those organizations that want to run Reporting Services in Native mode but still want to display Reporting Services content through SharePoint, the Reporting Services 2.0 web parts provide an alternative to SharePoint Integrated mode. <br />
<div>
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-34387069659416510882015-03-28T00:42:00.001-07:002020-01-17T05:23:53.297-08:00 SQL Server Editions<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<span style="font-family: "verdana" , sans-serif;">SQL Server 2008 comes in nine editions, the following seven of which include Reporting Services: </span><br />
<span style="font-family: "verdana" , sans-serif;"> </span><br />
<br />
<ol style="text-align: left;">
<li><span style="font-family: "verdana" , sans-serif;">Enterprise </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Standard </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Developer </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Enterprise Evaluation </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Workgroup </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Web </span></li>
<li><span style="font-family: "verdana" , sans-serif;">Express with Advanced Services </span></li>
</ol>
<br />
<span style="font-family: "verdana" , sans-serif;"> The Enterprise and Standard editions are the only editions supported in a production environment. The Enterprise edition provides access to the full set of features available with Reporting Services and runs on 32 - bit, x64 64- bit, and IA64 64 - bit platforms. The Standard edition provides access to a reduced feature set and does not support the IA64 64 - bit platform. It costs less than the Enterprise edition and may be more appropriate for smaller installations. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"> The Developer and Enterprise Evaluation editions provide access to the same features available through the Enterprise edition. The Developer edition is very inexpensive and is intended for development and testing environments only. The Enterprise Evaluation edition is free but expires after 180 days. These two editions support 32 - bit, x64 64 - bit, and IA64 64 - bit architectures and a wider range of operating systems than either the Enterprise or Standard edition. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"> Workgroup and Web editions support a reduced feature set, even more so than Standard edition, and reduced capacity as may be appropriate for small - scale or web - based deployments, respectively. Both editions support 32 - bit and x64 64 - bit platforms and have low licensing costs appropriate for their intended uses. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"> Finally, the Express with Advanced Services edition is a highly restricted edition of SQL Server with limited support for Reporting Services. This edition is freely available, but its limitations make it unlikely to be used for anything other than highly specialized needs. The Express with Advanced Services edition is available on 32 - bit and x64 - bit platforms. </span><br />
<div>
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-16609242946758414002015-03-28T00:19:00.003-07:002020-01-17T05:24:15.304-08:00Dedicated Administrator Connection<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="text-align: justify;">
<b><span style="line-height: 115%;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></span></b></div>
<div class="MsoNormal" style="text-align: justify;">
<b><span style="line-height: 115%;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Dedicated
Administrator Connection<o:p></o:p></span></span></b></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> It is rare, but SQL Server can stop responding to normal
queries and appear to be frozen. How could this happen? Consider that when you
bought the system that houses SQL Server, you likely planned for future growth
and usage. No matter how much time and effort you put into this plan, though,
some companies just outgrow their servers too quickly, and the servers can’t
handle the workload. In a scenario like this, it is possible that SQL Server
might stop responding to normal queries. To troubleshoot this issue, or any
kind of problem where SQL Server just isn’t responding to normal calls, you
need the Dedicated Administrator Connection (DAC).<o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> The DAC is a special diagnostics connection that is always
available for connection, even under normal operating circumstances. As the
name implies, only administrators (members of the sysadmin server role) can
connect to the DAC to run diagnostic queries and troubleshoot problems. So, how
does it work?<o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> By default, SQL Server listens for normal queries on TCP
port 1433, so when a user runs a SELECT query, it is transmitted to the server
over port 1433. The DAC listens, by default, on TCP port 1434, so it is not
cluttered with user traffic. In other words, it is always free and available
for connections. Because the DAC is always available, it always consumes some
sys- tem resources, but these are kept to a minimum.<o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> Because the DAC consumes minimal resources and is meant to
be used only for diagnostic functions, some limitations exist on what you can
do with it:</span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="text-indent: -0.25in;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></span></div>
<ol style="text-align: left;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small; text-align: justify; text-indent: -0.25in;">To ensure available resources, only one DAC
connection is allowed per server. If you try to open a DAC connection to a
server that already has a DAC connection open, you will get error 17810, and
the connection will be denied.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small; text-align: justify; text-indent: -0.25in;">You can’t run any parallel commands or queries
from within the DAC. For example, you cannot run BACKUP or RESTORE.</span></li>
<li style="text-align: justify;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><span style="text-indent: -0.25in;">Because of resource constraints, the DAC is not
available in SQL Server 2005 Express Edition.</span></span></li>
<li style="text-align: justify;"><span style="font-family: "verdana" , sans-serif; font-size: x-small; text-indent: -0.25in;">Because of the limited resources available, you
should not run complex queries in the DAC, such as large queries with complex
joins.</span></li>
<li style="text-align: justify;"><span style="font-family: "verdana" , sans-serif; font-size: x-small; text-indent: -0.25in;">By default, you cannot access the DAC from a
remote machine; you must be on the server. You can change this by setting the
remote admin connections option to 1 by using the sp_configure stored
procedure, like this:</span></li>
</ol>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">sp_configure ‘remote admin connections’, 1 <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">RECONFIGURE <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><o:p> </o:p><o:p> </o:p> </span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">That seems like a lot of restrictions, so
you may be wondering what you are allowed to do. The <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
<b><span style="font-family: "verdana" , sans-serif; font-size: x-small;">DAC
is especially good for these tasks:<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;">
</span><!--[endif]-->Querying dynamic management views: <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;">
</span><!--[endif]-->You can query sys.dm_exec_requests to find
blocking queries. <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;">
</span><!--[endif]-->You can query sys.dm_os_memory_cache_counters to
check the health of the system memory cache. <!--[if !supportFootnotes]--><span style="line-height: 115%;">[1]</span><!--[endif]-->
You can query sys.dm_exec_sessions for information about active sessions.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 56.25pt; text-align: justify;">
</div>
<ul>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"> Querying catalog views</span></li>
</ul>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Running basic DBCC commands: <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;"> </span>You can
use DBCC FREEPROCCACHE to remove all elements from the procedure cache.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;"> </span>You can
use DBCC FREESYSTEMCACHE to remove all unused entries from all caches.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;"> </span>You can
use DBCC DROPCLEANBUFFERS to remove all clean buffers from the buffer pool.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 74.25pt; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "verdana" , sans-serif; font-size: x-small;">·<span style="font-stretch: normal;"> </span>You can use DBCC SQLPERF to retrieve statistics
about how the transaction log space is used in all databases. <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="text-align: justify;">
</div>
<ul>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"> Using the KILL
command to end an errant session</span></li>
</ul>
<br />
<br />
<div class="MsoListParagraphCxSpLast" style="text-align: justify;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">
You can see that this is a
powerful weapon in your troubleshooting arsenal, but how do you use it? In this
task, you will work with the DAC.</span><o:p></o:p></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-62199988665674915422015-03-24T10:34:00.000-07:002020-01-17T05:24:45.665-08:00SQL Server 2012 Database Security Tasks.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">By taking appropriate precautions, most of these breaches can be prevented or detected before they get out of hand. </span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
</div>
<h2 style="color: #444444; line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="line-height: 1.538em;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;">1. Use a dedicated server for your database</span></span></h2>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Host your database on a dedicated server. Whether it is local or in the cloud, spend the extra cash on a dedicated server to prevent security leaks and breaches.</span></div>
<h2 style="color: #444444; line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">2. Harden the Operating System</span></h2>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">On your dedicated server, the first step is to implement operating system hardening.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Many hardening techniques exist. At a minimum, you need to:</span></div>
<ul style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Change the default ports, as described below.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Hide SQL instances from showing in the network, as described below.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Allow only network protocols that are needed.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">CONNECT permission should be granted only on endpoints to logins that need to use them.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self-signed certificates.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Avoid the exposure of SQL Server to the public internet/intranet.</span></li>
</ul>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Change default ports</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The first step in hardening the operating system is to change the default ports. A default installation of SQL Server 2012 use TCP port 1433 for client requests and communications. These ports are well known and are common targets for hackers.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To change the default ports</span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">From the Start menu, choose All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Expand the SQL Server 2012 Network Configuration node and select Protocols for the SQL Server instance to be configured.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In the right pane, right-click the protocol name TCP/IP and choose Properties.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In the TCP/IP Properties dialog box, select the IP Addresses tab.</span></li>
</ol>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">There is a corresponding entry for every IP address assigned to the server.</span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Clear the values for both the TCP Dynamic Ports and TCP Port for each IP address, except for the IP addresses under IPAll.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In the IPAll section for each instance, type in a new port for SQL Server 2012 requests and communications.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Click Apply.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restart the SQL Server Services.</span></li>
</ol>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> <span style="font-weight: bold; line-height: 1.538em;">Hide SQL instances from showing in the network</span></span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The SQL Server Browser service enumerates SQL Server information on the network. Attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To hide SQL instances:</span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">From the Start menu, choose All Programs, Microsoft SQL Server 2012, Configuration Tools, SQL Server Configuration Manager.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Expand the SQL Server 2012 Network Configuration node and select <strong>Protocols</strong> for the SQL Server instance to be configured.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Right-click <strong>Protocols for [Server\Instance Name]</strong> and choose <strong>Properties</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In the Hide Instance box on the Protocols for [Server\Instance Name] Properties page select<strong> Yes</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Click <strong>OK</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restart the services for the change to take effect.</span></li>
</ol>
<div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-weight: bold; line-height: 1.538em;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;">3. Control Admin Access to the Database - Increase <a href="http://www.greensql.com/control-database-access-ensure-security-and-compliance" style="color: #444444;">Role Based Access Control</a></span></span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> You should control not only the individuals who have access to the database, but also how administrators access the database.</span></div>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Administrator Privileges Control</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Elevated permissions are allowed not only for <strong>sysadmin</strong> users, but also any log in with built-in<strong>SA</strong>, and also any login with <strong>CONTROL SERVER</strong> permission. For accountability in the database, avoid relying on the Administrators group and add only specific database administrators to the <strong>sysadmin</strong> role. For a full description of best practices, see the official documentation by Microsoft entitled <em>SQL Server 2012 Security Best Practice Whitepaper</em>.</span></div>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Quick Tips for Admin Privileges</span></h3>
<ul style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Administrator privileges should be used only when they are really needed.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Have as few admins as possible.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not use one login for more than one administrator. Each admin should have his or her own account.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Provision admin principals explicitly.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not use the "BUILTIN\Administrators" Windows group.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Regularly audit to ensure only the appropriate authorized individuals have admin access privileges.</span></li>
</ul>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Removing the Builtin/Administrators Group</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Following is a Transact-SQL (T-SQL) syntax for removing the BUILTIN\Administrators Windows Group from a SQL Server instance. You should use this if a group exists from previous versions of SQL Server or using BETA code.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To remove the Builtin/Administrators Group, run the following code on each SQL Server instance installed in the organization:</span></div>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE MASTER</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">IF EXISTS (SELECT * FROM sys.server_principals</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">WHERE name = N’BUILTIN\Administrators’)</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">DROP LOGIN [BUILTIN\Administrators]</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Control Admin Access Routes to the Database</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Not only can you restrict the individuals who have admin access, but you can also restrict the routes of admin control. Using a tool such as <a href="http://www.greensql.com/" style="color: #444444;">GreenSQL</a>, you can ensure that access to admin privileges can come only from certain IP addresses or specific computers. This way, if someone leaves the company or if login information is compromised, it will be impossible for anyone else to use that login data.</span></div>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Managing Non-Administrative Users</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">It's important to manage users who do are not admin but have access to the database for other purposes. As with system administrators, it's important to not only give different authentication to different types of users, but also to control the routes of access to the database.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server instance can contain many databases which were created by users who are database owners -DBO (by default) as shown in the following image: User workshop created the workshop database and is a member of db_owner database role.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> Members of server role sysadmin are database owners with the db_owner role in every user database. Members of the db_owner role have approximately the same privileges as the dbo. </span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Best practices for non-administrator roles:</span></div>
<ul style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Minimize the number accounts/users that have the db_owner role for each database.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Have distinct owners for databases; not all databases should be owned by SA or by any other user in sysadmin server role.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Control the access methods and IP addresses for access of the database on a per-role basis.</span></li>
</ul>
<h2 style="color: #444444; line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">4. Encrypt the Data Between App and SQL Server 2012</span></h2>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The MS SQL database comes with built-in encryption within the database. However, it is also crucial to encrypt the data as it is passed between the app and the database. Furthermore, it's important to limit access to this information.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Best practices for encryption:</span></div>
<ul style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Ensure that DBAs and other people using the database do not have access to sensitive information.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">When sending information to users who do not need to know the actual content, mask the sensitive information.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Limit the amount of information that can be drawn from the database by those who have access to the database.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Set up rules to identify authorized and unauthorized use of data, including the IP addresses and routes for accessing data, not username-only authentication.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Set up encryption keys between applications and the database.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Implement cell-level encryption</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Implement Transparent Data Encryption</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Encrypt high-value and sensitive data.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Password-protect keys and remove master key encryption for the most secure configuration.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Always back up your database to back up your symmetric and asymmetric keys.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Perform SSL configuration</span></li>
</ul>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Cell Level Encryption</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Follow the SQL Server 2012 has an <a href="http://msdn2.microsoft.com/en-us/library/ms189586.aspx" style="color: #444444;" target="_blank">encryption hierarchy</a>, as shown below. </span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">The top-level resource in the SQL Server encryption hierarchy is the <a href="http://msdn2.microsoft.com/en-us/library/ms189060.aspx" style="color: #444444;" target="_blank">Service Master Key</a>, which is encrypted by the Windows Data Protection API. Encrypt all Service Master Keys.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Next is the Database Master Key. This key can be used to create certificates and asymmetric keys.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Third are certificates and asymmetric keys. Both can be used to create symmetric keys or encrypt data directly.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Finally, symmetric keys can also be used to encrypt data.</span></li>
</ol>
<div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To encrypt information on a cell level: </span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Create a Database Master Key.</strong> Before creating certificates (assuming the Database Master Key is used to create the certificate) or other keys can be generated, a Database Master Key must be created as follows:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE <DatabaseName><br />CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> 1. <strong style="line-height: 1.538em;">Backup Database Master Keys.</strong><strong style="line-height: 1.538em;"> </strong>Once a Database Master Key is created, back it up to a secure location, preferably offsite. Back up a Database Master Key as follows:</span></div>
</div>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE <DatabaseName><br />BACKUP MASTER KEY TO FILE = '<FolderLocation>'<br />ENCRYPTION BY PASSWORD = '<Password>'</span></div>
</div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Create a certificate </strong>for creating symmetric keys for data encryption or to encrypt the data directly, as follows:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE <DatabaseName><br />CREATE CERTIFICATE certXXXXX WITH SUBJECT='XXXXXX Certificate',<br />EXPIRY_DATE = '03/09/2018''</span></div>
</div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Backup the certificate as follows:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> USE <DatabaseName><br />BACKUP CERTIFICATE certXXXXX TO FILE = '<FileLocation>',<br />ENCRYPTION BY PASSWORD='<Password>'</span></div>
</div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Encrypt the data via certificate as follows:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">INSERT INTO [DatabaseName].[SomeTable]</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">values( N'data encrypted by certificate ''ThisIsSomething''', EncryptByCert(Cert_ID('certXXXXX'), @cleartext));</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<h3 style="color: #444444; line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">TDE – Transparent Data Encryption in SQL Server 2012 (Database Level Encryption)</span></h3>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">TDE provides real time encryption of data and log files. It is important to mention that this is <strong>database level encryption</strong>. Data is encrypted before it is written to disk and decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. </span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The database is prepared for TDE, and then the encryption is turned on at the database level via an ALTER DATBASE command. With TDE, the backup files are also encrypted when using just the standard BACKUP command.</span></div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> To enable TDE:</span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Create a Master Key.</strong> A master key is a symmetric key that is used to create certificates and asymmetric keys. Execute the following script to create a master key:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE master;</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">CREATE MASTER KEY</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">ENCRYPTION BY PASSWORD = 'Create^A#LongPassw@rd123';</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters).</span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Back up the Master key using BACKUP MASTER KEY and store it in a secure location.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Create a Certificate</strong>. Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:</span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> USE master;</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">CREATE CERTIFICATE TDECert</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">WITH SUBJECT = 'TDE Certificate'</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Backup the certificate using BACKUP CERTIFICATE and store it in a secure location.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"> <strong>Create a Database Encryption Key</strong>.A database encryption key is required for TDE. Execute the following script to create a new database and a database encryption key for it<strong>:</strong></span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">CREATE DATABASE mssqltips_tde</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE mssqltips_tde;</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">CREATE DATABASE ENCRYPTION KEY</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">WITH ALGORITHM = AES_256</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">ENCRYPTION BY SERVER CERTIFICATE TDECert</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<div style="color: #444444; line-height: 24px; margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To work with TDE, the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database. </span></div>
<ol style="color: #444444; line-height: 24px; margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Enable TDE</strong> by executing the following script<strong>:</strong></span></li>
</ol>
<div style="color: #444444; line-height: 24px;">
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">ALTER DATABASE mssqltips_tde</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SET ENCRYPTION ON</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SELECT [name], is_encrypted FROM sys.databases</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SSL Configuration in SQL Server</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> To Configure SSL for SQL Server:</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"> Install a certificate in the Windows certificate store of the server computer.</span></div>
<ol style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Click <strong>Start</strong>, in the <strong>Microsoft SQL Server</strong> program group, point to <strong>Configuration Tools</strong>, and then click <strong>SQL Server Configuration Manager</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Expand <strong>SQL Server Network Configuration</strong>, right-click the protocols for the server you want, and then click <strong>Properties.</strong></span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">On the <strong>Certificate</strong> tab, configure the Database Engine to use the certificate.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">On the <strong>Flags </strong>tab, view or specify the protocol encryption option. The login packet will always be encrypted.</span></li>
</ol>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">When the <strong>ForceEncryption</strong> option for the Database Engine is set to <strong>Yes</strong>, all client/server communication is encrypted and clients that cannot support encryption are denied access.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">When the <strong>ForceEncryption </strong>option for the Database Engine is set to <strong>No</strong>, encryption can be requested by the client application but is not required.</span></li>
</ul>
<ol style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restart SQL Server after you change the <strong>ForceEncryption </strong>setting.</span></li>
</ol>
<h2 style="line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">6. Reduce the Potential Attack Surface</span></h2>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Attack Surface refers to the potential entrances for attack. It's advisable only to enable the features that are essential for any given database.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server comes with several features that administrators can choose to install during the installation process:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Database Engine</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Reporting Services</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Integration Services</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Analysis Services Engine</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Notification Services</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Documentation and Samples (Sample databases & codes)</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Analyze your needs and install only the features you need.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Surface Area Reduction Practices</span></h3>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use the Surface Area Configuration Tool or sp_configure as described below.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not install sample databases and sample codes on SQL servers in the production environment.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use only development and test environments for sample databases and sample code on SQL servers.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use the Configuration tools such as sp_configure or SQL Server Surface Area Configuration tool (described below) to enable only needed features.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">When upgrading from SQL Server 2000 to 2005 and higher, review the configuration settings and turn off features such as the xp_cmdshell. The upgrade process does not change these settings by default.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Turn off unnecessary services by setting them to disabled or manual startup.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Disable unneeded system stored procedures as described below</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Document each exception to the standard policy.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not remove the system stored procedures by dropping them.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not DENY all users/administrators access to the extended procedures.</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-weight: bold; line-height: 1.538em;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server Surface Area Configuration Tool</span></span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server 2012 contains configuration tools such as a system stored procedure calledsp_configure and the SQL Server Surface Area Configuration tool (for services and features). These tools make it easier to enable and disable optional features. Typically the features are disabled by default.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Following are the features that can be enabled using the tool:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">xp_cmdshell</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server Web Assistant</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">CLR Integration</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">OLE Automation system procedures</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">System procedures for Database Mail and SQL Mail</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Remote use of a dedicated administrator connection</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The CLI version of the tool - sac.exe can be found at %Program Files%\Microsoft SQL Server\90\Shared. Using these tools you can import and export settings between several servers on the network. You must have sysadmin privilege to use this tool.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Following is an example of exporting all settings from the default instance of SQL Server on server A and importing them to server B:</span></div>
<div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">sac out serverA.out -S serverA -U admin -I MSSQLSERVER</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">sac in serverA.out -S serverB</span></div>
</div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The following code snippet describes how to remove SQL Server components by using shell commands. At the command prompt, run the following command:</span></div>
<div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove</span></div>
</div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Uninstall the SQL Server components one at a time until desired SQL Server components are uninstalled</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Stored Procedure: sp_configure</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>sp_configure</strong> is a system-stored procedure which can be used instead of the Surface Area Configuration Tool to enable and disable the features in SQL Server. Following is an example of using sp_configure to disable the xp_cmdshell command which enables to run shell commands on the server. The xp_cmdshell is turned off by default in SQL Server 2005 and on.</span></div>
<div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">EXEC sp_configure 'show advanced options', 1</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">RECONFIGURE</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">EXEC sp_configure 'xp_cmdshell', 0</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">RECONFIGURE</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
</div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Disable Unneeded System Stored Procedures</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server comes with various system stored procedures such as xp_cmdshell orsp_send_dbmail that interact with operating system or execute code outside of a normal SQL Server permissions and may constitute a security risks. It's important to disable those procedures you don’t need.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">In SQL Server 2005 it is possible to enable and disable these features in the Surface Area Configuration tool at <strong>Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> Surface Area Configuration tool</strong>.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The following features are disabled by default and should stay disabled:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Disable xp_cmdshell unless it is absolutely needed.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Disable COM components once all COM components have been converted to SQLCLR.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it.</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Notes:</strong> Some system stored procedures, such as procedures that use SQLDMO and SQLSMO libraries, cannot be configured by using SQL Server Surface Area Configuration. They must be configured by using s<strong>p_configure</strong> or The SQL Server Management Studio (SSMS) directly.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">In SQL Server 2012, the SAC was replaced by Policy Based Management framework. This could be accessed from the SSMS under the Management option. It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.</span></div>
<h2 style="line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">7. Implement Strong Authentication</span></h2>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use Windows Authentication mode, described below, when possible.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use Mixed Mode Authentication, described below, only for legacy applications and non-Windows users.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Authentication mode is described below, but it is NOT the recommended mode. It should be used only when in mixed mode, to leverage complex passwords and the SQL Server /2012 password and lockout policies</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Maintain a strong password policy for the SA account and change the password periodically.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not manage SQL Server using the sa login account. Assign sysadmin privilege to a knows user or group.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">When using Mixed Mode Authentication beware that potential attackers are aware of the SA user. Knowing the SA user makes cracking the database one step easier. To avoid this, in mixed mode, the SA account must be renamed. Before renaming make sure there is at least one additional account with administrator privileges, to access the SQL Account.<br />To rename the SA User:</span></li>
</ul>
<div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">USE MASTER</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">ALTER LOGIN sa DISABLE;</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">GO</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">ALTER LOGIN sa WITH NAME = [WinnerUser];</span></div>
</div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Note: Before renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Windows and SQL Authentication Modes</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server 2012 supports two modes for validating connections and authenticating access to database resources: Windows Authentication mode and SQL Server and Windows Authentication mode. Both authentication methods provide access to SQL Server and its resources. Windows authentication is preferred.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Windows Authentication Mode</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Windows Authentication mode is the default and recommended authentication mode. It leverages local accounts, Active Directory user accounts, and groups when granting access to SQL Server. In this mode, you, as the database administrator, can grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Another advantage of using Windows Authentication is management of password policy by the Active Directory or the local security policy.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Mixed Mode: SQL Server & Windows Authentication</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The SQL authentication mechanism is based on accounts that are managed inside the SQL server, including the password policy.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Mixed authentication (SQL Server and Windows Authentication mode) is still required if there is a need to support legacy applications, or if specific applications require mixed mode, or clients are coming in from platforms other than Windows and a need for separation of duties exists.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Configuring SQL Server Authentication Modes</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To select or change the server authentication mode, follow these steps:</span></div>
<ol style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In SQL Server Management, right-click on a SQL Server and click <strong>Properties</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">On the Security page, select the desired server authentication mode under Server Authentication and click <strong>OK</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In the SQL Server Management Studio dialog box, click <strong>OK</strong> to acknowledge the need to restart SQL Server.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">In Object Explorer, right-click on a desired server and then click <strong>Restart</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">If the SQL Server Agent is running, restart the agent.</span></li>
</ol>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Using Windows authentication is a more secure choice. However, if mixed mode authentication is required, you must make sure to leverage complex passwords and the SQL Server 2012 password and lockout policies to further bolster security.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Here is an example of password policy for SQL accounts:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">The password must contain uppercase & lowercase letters.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">The password must contain numbers & alphanumeric characters.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">The password must contain non-alphanumeric characters such as &, ^,%,*,$ etc.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Do not use common known passwords that are easy to guess such as: admin, password, sa, administrator, sysadmin etc.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Passwords contain a minimum of 8 characters.</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server 2005 and on do not allows blank password for the SA account. If you are using earlier version of SQL, set a password for SQL accounts and also for the SA account according to according to password policy.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><strong>Note:</strong> If Windows Authentication mode is selected during installation, the SA login is disabled by default. If the authentication mode is switched to SQL Server mixed mode after the installation, the SA account is still disabled and must be manually enabled. It is a best practice to reset the password when the mode is switched.</span></div>
<h2 style="line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">8. Perform Regular and Reliable Auditing</span></h2>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">For reliable auditing it is necessary to use a third-party tool such as Green SQL. Many companies think of auditing as something that must be done to comply with regulation. However, it's also an important internal security precaution in and of itself, and should be performed regularly. Therefore, it's recommended to choose a third-party auditing tool that is quick and simple to use.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Additional Instructions</span></h3>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Auditing is scenario-specific. Balance the need for auditing with the overhead of generating addition data.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Audit successful logins in addition to unsuccessful logins if you store highly sensitive data.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Enable C2 auditing or Common Criteria compliance only if required by selecting the appropriate checkbox (Those options should be selected only if there is a need to comply with these security standards)</span></li>
</ul>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Auditing Mechanism in SQL Server</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">None—Disables auditing (no events are logged)</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Successful Logins Only—Audits all successful login attempts</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Failed Logins Only—Audits all failed login attempts</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Both Failed and Successful Logins—Audits all login attempts</span></li>
</ul>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The default mode is: <strong>Failed Logins Only</strong>. It is recommended to set the auditing mode to be <strong>Both Failed and Successful Logins</strong>.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Configuring SQL Server Security Logs for Auditing</span></h3>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To configure security login auditing for both failed and successful logins:</span></div>
<ol style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;"> In SQL Server Management Studio, right-click on a desired SQL Server and then click<strong>Properties</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">On the Security page under Login Auditing, select the desired auditing criteria option button, such as Both Failed and Successful Logins, and click <strong>OK</strong>.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restart the SQL Server Database Engine and SQL Server Agent to make the auditing changes effective.</span></li>
</ol>
<div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-weight: bold; line-height: 1.538em;"><span style="font-family: "verdana" , sans-serif; font-size: x-small;">C2 Audits</span></span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To configure the C2 audit, activate (in Query Analyzer or osql.exe) and run the following (Command example):</span></div>
<div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">EXEC sp_configure 'show advanced option', '1'</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">go</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">RECONFIGURE</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">go</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">EXEC sp_configure 'c2 audit mode','1'</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">go</span></div>
<div class="Code" style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">RECONFIGURE</span></div>
</div>
<h2 style="line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">9. Update Patches Regularly</span></h2>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Security updates and patches are constantly being released by Microsoft. Install these updates made available for SQL Server and the operating system. These patches can be manually downloaded and installed, or they can be automatically applied by using Microsoft Update. It's recommended to test updates before applying to production systems, therefore many admins prefer not to use auto update.</span></div>
<h3 style="line-height: 1.282em; margin-bottom: 1.282em; margin-top: 1.282em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Best practices for Patch Updates</span></h3>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Always stay as current as possible.</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Enable automatic updates whenever feasible, but test them before applying to production systems.</span></li>
</ul>
<h2 style="line-height: 2em; margin-bottom: 1em; margin-top: 1em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">10. Manage Contained Databases for SQL Server 2012 Only</span></h2>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. This situation requires additional security steps. It's important to enable partially contained databases delegates control over access to the instance of SQL Server to the owners of the database.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The main concepts are covered in <a href="http://msdn.microsoft.com/en-us/library/ff929055.aspx" style="color: #444444;">Security Best Practices with Contained Databases</a>.</span></div>
<div style="margin-bottom: 1.5em; margin-top: 1.5em;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To summarize, for managing contained databases, you will need to:</span></div>
<ul style="margin: 1.5em 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Handle threats related to users, including:</span><ul style="margin: 0px; padding: 0px 0px 0px 30px;">
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Accessing other databases using a guest account</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Duplicate logins</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Changes in containment status of databases</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Attaching a contained database</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Password management</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Kerebos authentication</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Offline dictionary attacks</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Auditing of contained databases</span></li>
<li><span style="font-family: "verdana" , sans-serif; font-size: x-small;">Denial of services through AUTO_CLOSE</span></li>
</ul>
</li>
</ul>
</div>
</div>
</div>
</div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-15855731665310364532015-03-24T10:23:00.000-07:002020-01-17T05:25:05.671-08:00SQL Server - Table backup.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
Now we are going to see how we are going to take a table backup.<br />
<dl>
<dd><table border="1" bordercolor="#000000" cellpadding="7" cellspacing="0" style="width: 569px;">
<colgroup><col width="553"></col>
</colgroup><tbody>
<tr>
<td valign="TOP" width="553"><span lang="">Backup single table </span>
</td>
</tr>
<tr>
<td valign="TOP" width="553"><span style="color: blue;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">exec</span></span></span><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">
master</span></span><span style="color: grey;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">..</span></span></span><span style="color: maroon;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">xp_cmdshell</span></span></span><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">
</span></span><span style="color: red;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">'BCP
export1.dbo.tab1 out new.txt -c -U sa -P sa -S
ION-0481AD1D360\SQLTEST'</span></span></span></td>
</tr>
<tr>
<td valign="TOP" width="553"><span lang="">Restore from file to table</span></td>
</tr>
<tr>
<td valign="TOP" width="553"><span style="color: blue;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">exec</span></span></span><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">
master</span></span><span style="color: grey;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">..</span></span></span><span style="color: maroon;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">xp_cmdshell</span></span></span><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">
</span></span><span style="color: red;"><span style="font-family: "courier new" , monospace;"><span style="font-size: x-small;">'BCP
export1.dbo.tab1 in new.txt -c -U sa -P sa -S
ION-0481AD1D360\SQLTEST'</span></span></span></td>
</tr>
</tbody></table>
</dd></dl>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-65600502940272267252015-03-24T10:18:00.000-07:002020-01-17T05:25:41.823-08:00SQL Server - Database Mail Configuration.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white;"><br /></span>
<br />
<div class="MsoNormal" style="border: 0px; font-family: 'Lucida Sans Unicode', 'Lucida Grande', 'Bitstream Vera Sans', 'Trebuchet MS', Geneva, Arial, sans-serif; font-size: 12.1599998474121px; line-height: 19.6991996765137px; margin: 0in 0in 10pt; padding: 0px;">
<span style="background-color: white; font-size: small;">I recently had to setup Database Mail on dozens of SQL Server instances. Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here. </span></div>
<div class="MsoNormal" style="border: 0px; font-family: 'Lucida Sans Unicode', 'Lucida Grande', 'Bitstream Vera Sans', 'Trebuchet MS', Geneva, Arial, sans-serif; font-size: 12.1599998474121px; line-height: 19.6991996765137px; margin: 0in 0in 10pt; padding: 0px;">
<span style="background-color: white; font-size: small;">My needs were simple so I only needed a single SMTP account and profile. I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified. You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports.</span></div>
<h1 style="border: 0px; font-family: 'Lucida Sans Unicode', 'Lucida Grande', 'Bitstream Vera Sans', 'Trebuchet MS', Geneva, Arial, sans-serif; line-height: 27.9680004119873px; margin: 24pt 0in 0pt; padding: 0px; page-break-after: avoid;">
<span style="background-color: white; color: #365f91; font-family: "cambria" , serif; font-size: 14pt; line-height: 21.4666652679443px;">Setup Script<o:p></o:p></span></h1>
<div class="MsoNormal" style="border: 0px; font-family: 'Lucida Sans Unicode', 'Lucida Grande', 'Bitstream Vera Sans', 'Trebuchet MS', Geneva, Arial, sans-serif; font-size: 12.1599998474121px; line-height: 19.6991996765137px; margin: 0in 0in 10pt; padding: 0px;">
<span style="background-color: white; font-size: small;">Below is the template script I used for my task. The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment.</span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: medium none; color: black; font-family: 'Lucida Sans Unicode', 'Lucida Grande', 'Bitstream Vera Sans', 'Trebuchet MS', Geneva, Arial, sans-serif; font-size: 12.1599998474121px; line-height: 19.6991996765137px;"><tbody>
<tr><td style="background-color: transparent; border: 1pt solid red; padding: 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: green; font-family: "courier new"; font-size: 10pt;">-- Enable Database Mail for this instance</span><span style="color: blue; font-family: "courier new"; font-size: 10pt;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> <span style="color: maroon;">sp_configure</span><span style="color: blue;"> </span><span style="color: red;">'show advanced'</span><span style="color: grey;">,</span> 1<span style="color: grey;">;<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">RECONFIGURE</span><span style="color: grey; font-family: "courier new"; font-size: 10pt;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> <span style="color: maroon;">sp_configure</span><span style="color: blue;"> </span><span style="color: red;">'Database Mail XPs'</span><span style="color: grey;">,</span>1<span style="color: grey;">;<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">RECONFIGURE</span><span style="color: grey; font-family: "courier new"; font-size: 10pt;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: blue; font-family: "courier new"; font-size: 10pt;">GO<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<br /></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: green; font-family: "courier new"; font-size: 10pt;">-- Create a Database Mail account<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sysmail_add_account_sp<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;"> </span><span style="font-family: "courier new"; font-size: 10pt;">@account_name <span style="color: grey;">=</span> <span style="color: red;">'Primary Account'</span><span style="color: grey;">,<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @description <span style="color: grey;">=</span> <span style="color: red;">'Account used by all mail profiles.'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @email_address <span style="color: grey;">=</span> <span style="color: red;">'xyz@yahoo.com'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @replyto_address <span style="color: grey;">=</span> <span style="color: red;">'abc@gmail.com'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @display_name <span style="color: grey;">=</span> <span style="color: red;">'Database Mail'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @mailserver_name <span style="color: grey;">=</span> 'smtp<span style="color: red;">.gmail.com'</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<br /></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: green; font-family: "courier new"; font-size: 10pt;">-- Create a Database Mail profile<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sysmail_add_profile_sp<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;"> </span><span style="font-family: "courier new"; font-size: 10pt;">@profile_name <span style="color: grey;">=</span> <span style="color: red;">'Default Public Profile'</span><span style="color: grey;">,<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @description <span style="color: grey;">=</span> <span style="color: red;">'Default public profile for all users'</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<br /></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: green; font-family: "courier new"; font-size: 10pt;">-- Add the account to the profile<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sysmail_add_profileaccount_sp<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;"> </span><span style="font-family: "courier new"; font-size: 10pt;">@profile_name <span style="color: grey;">=</span> <span style="color: red;">'Default Public Profile'</span><span style="color: grey;">,<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @account_name <span style="color: grey;">=</span> <span style="color: red;">'Primary Account'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @sequence_number <span style="color: grey;">=</span> 1<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<br /></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: green; font-family: "courier new"; font-size: 10pt;">-- Grant access to the profile to all msdb database users<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sysmail_add_principalprofile_sp<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;"> </span><span style="font-family: "courier new"; font-size: 10pt;">@profile_name <span style="color: grey;">=</span> <span style="color: red;">'Default Public Profile'</span><span style="color: grey;">,<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @principal_name <span style="color: grey;">=</span> <span style="color: red;">'public'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @is_default <span style="color: grey;">=</span> 1<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: blue; font-family: "courier new"; font-size: 10pt;">GO<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<br /></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: green; font-family: "courier new"; font-size: 10pt;">--send a test email<o:p></o:p></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;">EXECUTE</span><span style="font-family: "courier new"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sp_send_dbmail<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white;"><span style="color: blue; font-family: "courier new"; font-size: 10pt;"> </span><span style="font-family: "courier new"; font-size: 10pt;">@subject <span style="color: grey;">=</span> <span style="color: red;">'Test Database Mail Message'</span><span style="color: grey;">,<o:p></o:p></span></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @recipients <span style="color: grey;">=</span> <span style="color: red;">'xyz@yahoo.com'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; font-family: "courier new"; font-size: 10pt;"> @query <span style="color: grey;">=</span> <span style="color: red;">'SELECT @@SERVERNAME'</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="border: 0px; margin: 0in 0in 0pt; padding: 0px;">
<span style="background-color: white; color: blue; font-family: "courier new"; font-size: 10pt;">GO</span></div>
</td></tr>
</tbody></table>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-74284903750429530772015-03-24T10:12:00.002-07:002020-01-17T05:26:07.199-08:00SQL Server - Transaction Log Backups.<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="CENTER" lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="color: red; font-family: "verdana" , sans-serif; font-size: x-small;">Tail-Log Backups</span></div>
<div align="CENTER" lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">This topic is relevant only for databases
that are using the full or bulk-logged recovery models.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">In most cases, under the full or bulk-logged
recovery models, SQL Server 2005 and later versions require that you
back up the tail of the log to capture the log records that have not
yet been backed up. A log backup taken of the tail of the log just
before a restore operation is called a tail-log backup.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">SQL Server 2005 and later versions usually
require that you take a tail-log backup before you start to restore a
database. The tail-log backup prevents work loss and keeps the log
chain intact. When you are recovering a database to the point of a
failure, the tail-log backup is the last backup of interest in the
recovery plan. If you cannot back up the tail of the log, you can
recover a database only to the end of the last backup that was
created before the failure.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Not all restore scenarios require a tail-log
backup. You do not have to have a tail-log backup if the recovery
point is contained in an earlier log backup, or if you are moving or
replacing (overwriting) the database and do not need to restore it to
a point of time after the most recent backup. Also, if the log files
are damaged and a tail-log backup cannot be created, you must restore
the database without using a tail-log backup. Any transactions
committed after the latest log backup are lost. For more information,
see "Restoring Without Using a Tail-Log Backup" later in
this topic.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Backing Up the Tail of the Log
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Like any log backup, a tail-log backup is
taken by using the BACKUP LOG statement. We recommend that you take a
tail-log backup in the following situations:
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">If the database is online and you plan to
perform a restore operation on the database, before starting the
restore operation, back up the tail of the log using WITH NORECOVERY:
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="color: #cc0000; font-family: "verdana" , sans-serif; font-size: x-small;">BACKUP LOG database_name TO <backup_device>
WITH NORECOVERY</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Note
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To avoid an error, the NORECOVERY option is
necessary.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">If the database is offline and does not
start.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Try to take a tail-log backup. Because no
transactions can occur at this time, using WITH NORECOVERY is
optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR,
as follows:
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">BACKUP LOG database_name TO <backup_device>
WITH CONTINUE_AFTER_ERROR
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">If the database is damaged, for example, if
the database does not start, a tail-log backup succeeds only if the
log files are undamaged, the database is in a state that supports
tail-log backups, and the database does not contain any bulk-logged
changes.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The following table summarizes these
options.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">BACKUP LOG option</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Comments
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">NORECOVERY</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use NORECOVERY whenever you intend to
continue with a restore operation on the database. NORECOVERY takes
the database into the restoring state. This guarantees that the
database does not change after the tail-log backup.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">The log is truncated unless the NO_TRUNCATE
option or COPY_ONLY option is also specified.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Important</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">We recommend that you avoid using
NO_TRUNCATE, except when the database is damaged.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">CONTINUE_AFTER_ERROR</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Use CONTINUE_AFTER_ERROR only if you are
backing up the tail of a damaged database.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Note</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">When you use back up the tail of the log on
a damaged database, some of the metadata ordinarily captured in log
backups might be unavailable. For more information, see "Tail-Log
Backups with Incomplete Backup Metadata," later in this topic.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To create a transaction log backup when the
database is damaged</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">How to: Back Up the Transaction Log When the
Database Is Damaged (Transact-SQL)</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">How to: Back Up the Tail of the Transaction
Log (SQL Server Management Studio)</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Tail-Log Backups That Have Incomplete
Backup Metadata
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Tail log backups capture the tail of the log
even if the database is offline, damaged, or missing data files. This
might cause incomplete metadata from the restore information commands
and msdb. However, only the metadata is incomplete; the captured log
is complete and usable.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">If a tail-log backup has incomplete
metadata, in the backupset table, has_incomplete_metadata is set to
1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata
is set to 1.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br /></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">If the metadata in a tail-log backup is
incomplete, the backupfilegroup table will be missing most of the
information about filegroups at the time of the tail-log backup. Most
of the backupfilegroup table columns are NULL; the only meaningful
columns are as follows:
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">backup_set_id</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">filegroup_id</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">type</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">type_desc</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">is_readonly
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restoring Without Using a Tail-Log Backup
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restore scenarios in which a tail-log backup
is unnecessary include the following:</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><b>Restoring a database to a point in time that
is contained in a previous log backup. </b>
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">A tail-log backup is unnecessary if you
restore a database and specify the STOPAT, STOPATMARK, or
STOPBEFOREMARK option in every RESTORE statement in your restore
sequence.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><b>To restore a database to an earlier point in
time</b></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To use Transact-SQL to restore to a specific
point in time, see How to: Restore to a Point in Time (Transact-SQL),
Recovering to a Marked Transaction, or Recovering to a Log Sequence
Number (LSN).
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">To use SQL Server Management Studio, see How
to: Restore to a Point in Time (SQL Server Management Studio) or How
to: Restore a Database to a Marked Transaction (SQL Server Management
Studio).</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><b>Restoring a copy of a database to a new
location. </b>
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">When you restore a database, you can use the
same database name only if you restore the database to a different
server instance, such as when you create a mirror database for
database mirroring or a secondary database for log shipping. If you
move a database on the same server instance, you must specify a new
name for the database.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><b>To restore a database to a new location</b></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Using Transact-SQL, in every RESTORE
statement in your restore sequence, specify the MOVE option. For more
information, see How to: Restore a Database to a New Location and
Name (Transact-SQL) or How to: Restore Files to a New Location
(Transact-SQL).</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Using SQL Server Management Studio, specify
the new location of each file in the Restore As field on the Restore
Database (Options Page). For more information, see How to: Restore a
Database Backup (SQL Server Management Studio).</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Completely replacing (overwriting) the
database.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Caution
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Restoring with the REPLACE option should be
used rarely and only by experienced database administrators, after
careful consideration. For more information, see Using the REPLACE
Option.</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><b>To replace a database</b></span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Using Transact-SQL, specify the REPLACE
option in your RESTORE statements.
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;"><br />
</span></div>
<div lang="" style="line-height: 100%; margin-bottom: 0in; margin-left: 0.5in;">
<span style="font-family: "verdana" , sans-serif; font-size: x-small;">Using SQL Server Management Studio, specify
the new location of each file in the Restore As field on the Restore
Database (Options Page). For more information, see How to: Restore a
Database Backup (SQL Server Management Studio).</span></div>
<br />
<div lang="" style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-83522667797924308312015-03-24T10:06:00.005-07:002020-01-17T05:26:23.910-08:00SQL Server - Write Ahead Log.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="line-height: 100%; margin-bottom: 0.16in; margin-left: 0.25in; margin-top: 0.19in;">
<span style="font-family: "times new roman" , serif;"><span style="font-size: small;"><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"> </span></span></span></span></span></div>
<div style="line-height: 100%; margin-bottom: 0.16in; margin-left: 0.25in; margin-top: 0.19in;">
<span style="font-family: "times new roman" , serif;"><span style="font-size: small;"><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"> This topic describes the role of the write -ahead transaction log in recording data modification to disk. For basic information about transaction logs, </span></span></span></span></span><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">see </span></span></span><span style="color: blue;"><u><a href="http://msdn.microsoft.com/en-us/library/ms190925.aspx"><span style="color: #1364c4;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">Introduction to Transaction Logs</span></span></span></a></u></span><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">.</span></span></span></div>
<div style="line-height: 100%; margin-bottom: 0.16in; margin-left: 0.25in; margin-top: 0.19in;">
<span style="color: black; font-family: "times new roman" , serif; line-height: 100%;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">SQL
Server uses a write-ahead log (WAL), which guarantees that no data
modifications are written to disk before the associated log record is
written to disk. This maintains the ACID properties for a
transaction. For more information about transactions and ACID
properties, see</span></span></span><span style="color: black; font-family: "times new roman" , serif; line-height: 100%;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"> </span></span></span><span style="color: blue; font-family: "times new roman" , serif; line-height: 100%;"><u><a href="http://msdn.microsoft.com/en-us/library/ms190612.aspx"><span style="color: #1364c4;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">Transactions
(Database Engine)</span></span></span></a></u></span><span style="color: black; font-family: "times new roman" , serif; line-height: 100%;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">.</span></span></span></div>
<div style="line-height: 100%; margin-bottom: 0.16in; margin-left: 0.25in; margin-top: 0.19in;">
<span style="font-family: "times new roman" , serif;"><span style="font-size: small;"><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">To
understand how the write-ahead log works, it is important for you to
know how modified data is written to disk. SQL Server maintains a
buffer cache into which it reads data pages when data must be
retrieved. Data modifications are not made directly to disk, but are
made to the copy of the page in the buffer cache. The modification is
not written to disk until a checkpoint occurs in the database, or the
modification must be written to disk so the buffer can be used to
hold a new page. Writing a modified data page from the buffer cache
to disk is called flushing the page. A page modified in the cache,
but not yet written to disk, is called d</span></span></span><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"><i>irty
page</i></span></span></span><span style="color: black;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">.</span></span></span></span></span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0.16in; margin-left: 0.25in; margin-top: 0.19in;">
<span style="font-family: "times new roman" , serif;"><span style="font-size: small;"><span style="color: #c00000;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">At
the time a modification is made to a page in the buffer, a log record
is built in the log cache that records the modification. This log
record must be written to disk before the associated dirty page is
flushed from the buffer cache to disk. If the dirty page is flushed
before the </span></span></span><span style="color: #c00000;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"><b>log
record</b></span></span></span><span style="color: #c00000;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">
is written, the dirty page creates a modification on the disk that
cannot be rolled back if the server fails before the </span></span></span><span style="color: #c00000;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;"><b>log
record</b></span></span></span><span style="color: #c00000;"><span style="font-family: "segoe ui" , sans-serif;"><span style="font-size: x-small;">
is written to disk. SQL Server has logic that prevents a dirty
page from being flushed before the associated log record is written.
Log records are written to disk when the transactions are committed.</span></span></span></span></span></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-31585074006171358272015-03-24T09:33:00.001-07:002020-01-17T05:27:03.990-08:00Troubleshooting SQL Server Backup/Restore.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>1. Ensure that you are using the latest SQL Server service pack.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"> </span><span style="background-color: #ffffe1; font-family: "courier new" , "courier" , monospace; font-size: 12px;">SELECT @@VERSION</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>2. Ensure that you have the appropriate permissions to make a backup/restore.</strong></span></div>
<div style="background-color: white;">
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">To backup a database or transaction log, you should have BACKUP DATABASE or BACKUP LOG permissions granted to you, or you should be a member of the sysadmin fixed server role, or of the db_owner or db_backup operator fixed database roles.</span></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">To restore a database, you should have CREATE DATABASE permissions if the database being restored does not exist. If the database being restored does exist, you should have RESTORE permissions granted to you, or you should be a member of the sysadmin or dbcreator fixed server roles, or you need to be the owner (dbo) of the database.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>3. If you want to make a backup of the transaction log, check that the 'trunc. log on chkpt' option is turned off.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">You cannot make a backup of the transaction log if the 'trunc. log on chkpt' option is turned on, because in this case the transaction log will be truncated on checkpoint and will be empty.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>4. If you encountered <em>Error 156</em> during backup or restore operations, before attempting additional BACKUP or RESTORE statements, set the SQL Server compatibility level to 70 if you work with SQL Server 7.0, or to 70 or 80 if you work with SQL Server 2000.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 156 indicates that the syntax of a Transact-SQL statement is incorrect. One of the causes for this error may be that the database is in SQL Server version 6.5 compatibility mode. Because the BACKUP and RESTORE keywords are valid only with SQL Server 7.0 and SQL Server 2000 databases, you should set the SQL Server compatibility level to 70 or 80.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>5. If you encountered <em>Error 1511</em> during restoring a database or transaction log dump, enable trace flag 3222 to disable the read ahead that is used by the recovery operation during roll forward operations.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">To disable the read ahead, you can issue DBCC TRACEON (3222) on the same connection on which you attempt the restore operation. This is a known SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>6. If you encountered <em>Error 3023</em> during a backup operation, reissue the backup operation after the conflicting operation has completed.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 3023 indicates that the BACKUP statement cannot be performed at the same time as creating, deleting, or shrinking database files. So, to resolve this error you should reissue the backup operation after the conflicting operation has finished.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>7. If you encountered <em>Error 3036</em> during a backup operation, use backups from your primary server until operations have switched to the standby.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 3036 indicates that the database is in the warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>8. If you encountered <em>Error 3143</em> during a restore operation, use RESTORE HEADERONLY to determine the backup contents.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 3143 indicates that the backup being restored is a valid Microsoft Tape Format but is not a SQL Server backup. This error may arise when the backup may have been written by another software product. In this case, use RESTORE HEADERONLY to determine the backup contents.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>9. If you encountered <em>Error 3154</em> during a restore operation, overwrite the existing database by reissuing the RESTORE DATABASE command using the WITH REPLACE clause, or restore the backup set to a different database name.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 3154 indicates that you tried to restore database over an existing database, but the existing database was created by a different CREATE DATABASE statement than the database in the backup set.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>10. If you encountered error 3206 or error 3209 during backup operation, define the device using sp_addumpdevice, or refer to the physical device directly by specifying the TAPE = or DISK = syntax of the BACKUP statement.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">These errors indicate that you have attempted to use a logical device that is not a backup device.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>11. If you encountered <em>Error 3249</em> during a restore operation, replace the current volume with a volume containing the start of the target backup set.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 3249 indicates that the media family spans multiple volumes, and the backup set to be processed by the restore operation starts on an earlier volume than the one inserted into the named device.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>12. If you encountered <em>Error 3256</em> during a restore operation, restore a different database backup and use log backups to roll forward, if you were restoring a database backup; otherwise, restore the next log backup if you were restoring a log backup.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">This error indicates that the backup set is not complete because the backup operation that created the backup set did not finish successfully.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>13. If you encountered <em>Error 3257</em> during restoring a large database on Windows 98, try to create a database with the same size of the database from which the backup was created and then restore over the newly created database, or turn on trace flag 3104 and then restore the database.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">This error occurs when the file size of the restored database is 2 GB or greater and the database is being restored over an existing database that is less than 2 GB. This error indicates that SQL Server cannot correctly check the free space. To bypass checking for free space, you can issue DBCC TRACEON (3104) on the same connection on which you are attempting the restore operation.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>14. If you encountered <em>Error 3267</em> or <em>Error 3627</em> during a backup or restore operation, retry the operation after reducing the server load.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">These errors indicate the server is too busy to perform the backup or restore operation.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>15. Perform a full database backup before backing up the transaction log. Otherwise, you can encounter <em>Error 4214</em> during the restore operation.</strong></span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 4214 indicates that there is no current database backup. To restore the database after failure, you should have a full database backup or a complete set of file backups.</span></div>
<div style="background-color: white; font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /><strong>16. If you encountered <em>Error 4305</em> during a restore operation, restore the transaction log backups in the order they were created.</strong></span></div>
<div style="background-color: white;">
<div style="font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;">The error 4305 indicates that the restore operation found a gap between the last restore and the transaction log that you attempted to apply. So, to resolve this error you should restore transaction logs in the same order in which they were backed up.</span></div>
<div style="font-size: 13px;">
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">Backup Types in SQL Server</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">https://youtu.be/jESgGFNMUlo</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span>
<span style="font-size: 13px;"><span style="font-family: "verdana" , sans-serif;"></span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">Please Like, Subscribe, share the video for more videos...!!!</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span>
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span>
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">Restoring a Database in SQL Server</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">https://youtu.be/EluJIqEYsn8</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span>
<br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;">Please Like, Subscribe, share the video for more videos...!!!</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span>
<span style="font-family: "verdana" , sans-serif;"><span style="font-size: 13px;"><br /></span></span></div>
</div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0tag:blogger.com,1999:blog-8365221820735109490.post-75469719492978330312015-03-10T12:01:00.002-07:002020-01-17T05:27:43.253-08:00SQL Logins<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"> Logins are the credentials that authenticate
connections to an instance. Except in the case of an instance
configured to support contained databases, a database user must map
to an existing SQL Server login. You can differentiate SQL Server
logins based on the type of authentication method used. SQL Server
2012 supports the following login types: </span></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">■ Windows-authenticated
login</span></span></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">■ SQL Server–authenticated login</span></span></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">■ Certificate</span></span></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">■
Asymmetric key </span></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">A security principal must have the ALTER ANY LOGIN
permission to be able to create SQL logins.</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="margin-bottom: 0in; text-align: justify;">
</div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"><span style="color: #351c75;"><br /></span></span></span>
</div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"><span style="color: #351c75;">
</span></span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"><span style="color: #351c75;"><b>Windows-Authenticated SQL Server Logins:</b></span></span></span></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"><b> </b> Windows-authenticated SQL
Server logins are instance logins in which the operating system
handles authentication. You can map a Windows-authenticated SQL
Server login to a local user account, a local security group, a
domain user account, or a domain security group.</span></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">To create a Windows-authenticated SQL Server login, use the CREATE
LOGIN Transact-SQL statement with the FROM WINDOWS option. For
example, to create a SQL Server login by using the local account
Local_One on the server SQL-A, use the Transact-SQL statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">CREATE LOGIN "SQL-A\Local_One" FROM WINDOWS;</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">To create a SQL Server login using the local security group
Group_One on the server SQL-A, use the Transact-SQL statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">CREATE LOGIN "SQL-A\Group_One" FROM WINDOWS;</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">To create a SQL Server login using the domain account Account_Two
from the domain Contoso, use the Transact-SQL statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">CREATE LOGIN "CONTOSO\Account_Two" FROM WINDOWS;</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">To create a SQL Server login using the domain security group
Group_Two from the domain Contoso, use the Transact-SQL statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">CREATE LOGIN "CONTOSO\Group_Two" FROM WINDOWS;</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"> </span></span></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"><span style="color: #351c75;"><b>SQL Server–Authenticated Logins</b></span>:</span></span></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"> SQL Server–authenticated
logins are authenticated by the Database Engine instance rather than
through the host operating system or a domain controller. SQL
Server–authenticated login passwords are stored within the master
database. If the SQL Server authentica- tion option button is
disabled, you must configure the instance to support mixed-mode
authentication.
</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">You can create a new SQL Server–authenticated login using the
CREATE LOGIN statement and the WITH PASSWORD option. For example, to
create a SQL Server–authenticated login named sql_user_a with the
password Pa$$w0rd, execute the following statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">CREATE LOGIN sql_user_a WITH PASSWORD = 'Pa$$w0rd';</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">You can create a SQL Server–authenticated login to use the
password expiration and com- plexity policies that apply to the host
operating system by using the following options with the CREATE LOGIN
statement:</span></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"> ■ CHECK_EXPIRATION Enables you to configure SQL
Server–authenticated logins so that the password expiration policy
that applies to the host operating system applies to the login. This
option can be set to ON or OFF with the default value of OFF.</span></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;"> ■
CHECK_POLICY Enables you to configure SQL Server–authenticated
logins so that the password complexity policy that applies to the
host operating system applies to the login. Password complexity
policy includes minimum and maximum password lengths and whether the
password must contain a mix of uppercase, lowercase, numeric, and
symbol characters. This option can be set to ON or OFF with the
default value of ON.
</span></span></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="text-align: justify;">
<br /></div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span>
<br />
<div style="margin-bottom: 0in; text-align: justify;">
</div>
<span style="font-size: x-small;"><span style="font-family: "verdana" , sans-serif;">
</span></span></div>
Suda Sandeep Kumarhttp://www.blogger.com/profile/07495590833031270704noreply@blogger.com0